How many PARTITIONS for a hash partitioned table would be a REASONABLE number?

My goal is to optimize queries like this on a huge table A:

SELECT A.a1, B.b1
FROM A,B
WHERE
  A.a2=B.b2 and
  A.a3 in (331,811,955,631,1532,2072,2907,2875,3113,3067,3153,17267,4288,4446,4447,16300,16531,16576,16577,16948,17029) and
  A.a4 like 'MATCHES%'

And I consider using HASH partitioning of the A table (with partition pruning) like this:

CREATE TABLE A (a1,a2,a3,...) PARTITION BY HASH(a3) PARTITIONS 2600;

The A table has about 77.000.000 records and some 5 indexes. The volume of the A table is about 11Gb of extents. (The B table is notably smaller than A.) The A.a3 column has about 2600 different values and the query being optimized runs about 30 minutes without partitions. But it runs 20 seconds only on 2600 partitions! So, performance gets much better, but I am afraid Oracle will not manage such a bug number of partitions well.

The questions are: is 2600 partitions -- a reasonable number of partitions for Oracle 8.1.7.4? As I know, Oracle allows having 64000 partitions. Does anyone has experience of having LARGE number of partitions? I know also that the number of partitions should be a power of 2. Ok, 2048 or 4096 will also do. But, probably, 1024 or 512 will also do without affecting performance?

Please help me to choose between {performance} and {reliability+manageability}.
(I could not find corressponding article at Thomas Kite's site.)

Regards, Sergey Lukashevich, DBA Received on Wed Apr 06 2005 - 09:21:01 CDT