Skip to Main Content
  • Questions
  • Most of the rows are going to one single partition

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Girish.

Asked: February 13, 2017 - 8:13 am UTC

Last updated: February 13, 2017 - 8:44 am UTC

Version: 12.1.0.1.0

Viewed 1000+ times

You Asked

Hi,

We have hash partitioned table with 32 partitions and table has 25 million records.Most of inserted records are going one single partitions in this case 17 million are going to one partition.Is there way this can be controlled so that rows are distributed across partitions ?.Can any kind of reorganizations of table can be done to redistribute rows ?

Thanks,
Girish

and Connor said...

This sounds like you've chosen an inappropriate key to hash on.

Generally hash partitions will be evenly populated because the hash key will have many distinct values.

eg (in this case, 8 hash values for 100,000 distinct rows)

SQL> select ora_hash(rownum,7), count(*)
  2  from dual
  3  connect by level <= 100000
  4  group by ora_hash(rownum,7)
  5  order by 1;

ORA_HASH(ROWNUM,7)   COUNT(*)
------------------ ----------
                 0      12603
                 1      12575
                 2      12581
                 3      12508
                 4      12342
                 5      12381
                 6      12628
                 7      12382



But if the hash key source is not like that, eg, not many distinct values, then you could have skew, eg

SQL> select ora_hash(mod(rownum,6),7), count(*)
  2  from dual
  3  connect by level <= 100000
  4  group by ora_hash(mod(rownum,6),7)
  5  order by 1;

ORA_HASH(MOD(ROWNUM,6),7)   COUNT(*)
------------------------- ----------
                        2      16666
                        3      33334
                        5      16666
                        6      16667
                        7      16667



Is this answer out of date? If it is, please let us know via a Comment