I have seen cases of skew where there have been a few extremely popular values, in general however my experience is that the more values, the better the distribution.

I'm not sure that's was his question. I think his worry was the performance of the hash function with a very large distribution of keys in one column. Now I suspect you can't answer that as its the seed values that will determine the effectiveness of the hash not the number. So I suspect that I was just checking for your feeling about the quality of the hash functions they are using I imagine by now they are getting pretty good as they have had hash stuff for a while now!

There is no restriction on the number of values for the partitioning key for hash partitioning. However, the key must have no more than 16 columns, and the number of partitions must be a binary power less than or equal to 32K.

Do you have any strong comments on this one?

Assume most tables that we deal with have two main key columns that we query on, Time & Product_Id

It obviously makes sense to range partition by Time for archiving and purging purposes. However to get the best performance out of the box we really want to evenly distribute the products across all disks. Therefore I have proposed using a hash sub-partition.

The problem I have, is that I'm not sure of Oracle's limitations on the number of different values that can assigned as the hash key. In our case the Product_Id has approx 1.5-1.8 mil different values. Is this too many? All the examples in Oracle documentation seem to use only a limited number of values for the hash key, in the order of 50-100. No where in the doco or after ringing Oracle Support do we find any info on this.

We will obviously be testing this but any heads up info would be great.