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

I've had huge hash-partitioned tables on Oracle 9i. Another thing that we considered is the cardinality of each values.

In other words, you may want to find out -

select a3, count(*) num_a3 from A
group by a3
order by 2 desc;

Are the num_a3 s closely spaced? As in, do most of the values occur about similar number of times? Because if some of the a3 distinct values are very few in number (as in a particular value occuring just 3 times) - then you can do with a lesser number of partitions as well. Hash partitioning will work the best when number of appearance for each num_a3 is close to each other.

For your concerns regarding large number of partitions - I've seen Oracle performing just fine with thousands of partitions.

Did you try benchmarking the query with a global index created on A3 as well? Received on Wed Apr 06 2005 - 17:48:31 CDT