Table Partitioning

[ckwan]

Hi All,
I have few questions on the table partitioning. Someone created a table which has 7 hash partition, but I noticed that the partition 4 is higher than the rest. What is the reason for this ?
Anyone have ideas on add hash subpartition in the hash table ?

Thanks

[marist89]

Hash partitions are not guaranteed to be evenly distributed.

See Oracle Docs for details on how to add a partition.

[jmodic]

Also, if you use hash partitioning, the number of partitions should be in power of 2. So it woul dbe wise to have 8 partitions instead of 7 as you have now.

[tamilselvan]

It indicates that the column chosen for hash partition is not the right choice.

[jmodic]

Originally posted by tamilselvan
It indicates that the column chosen for hash partition is not the right choice.

Much more likely it is the consequence of the fact that the number of partitions is not being a power of two. Any other number of hash partitions leads to unbalanced partitioning. It's because Oracle internal hash partitioning alghoritm revolves around powers of two. The whole concept is perfectly and in details explained in Jothan Lewis's book "Practical Oracle8i". Here is a strong recomendation from that chapter:

"To ensure uniform data distribution across hash partitions you should always specify the number of partitions to be a power of two (2, 4, 8, 16, 32, asn so on). If you do not do this, some partitions are twice the size of other partitions."