Many of use has been using hash partitions just to evenly distribute the data based on partition key. The most common reason to adopt the hash partitioning is for performance reasons when data is distributed in partitions. But how many of use though that why oracle gives the recommendation while making a hash partition you should give power of 2 numbers of partitions?
Well below is the reason why oracle gave such recommendations. Its just because based on hashing algorithm which evenly distributes the data with power of 2, otherwise not.
SQL> create table test.hash_partition (id, name) partition by hash(id) partitions 10 as select rownum,object_na
me from dba_objects where rownum <5000;
So will try making them as 10 partitions initially (which is not power of 2) and see how many rows are distributed in each partitions.
SQL> select partition_position, partition_name, num_rows
2 from dba_tab_partitions where table_name='HASH_PARTITION';
PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------------------ ----------
1 SYS_P41 317
2 SYS_P42 304
3 SYS_P43 657
4 SYS_P44 591
5 SYS_P45 599
6 SYS_P46 653
7 SYS_P47 629
8 SYS_P48 646
9 SYS_P49 311
10 SYS_P50 292
As its visible, from partition 3 to 8 data is quite high then others. These are 6 partitions , so if we add 6 more partitions directly we might get even data distribution.
But we hold up and try to add just one.
SQL> alter table test.hash_partition add partition;
Table altered.
SQL> select partition_position, partition_name, num_rows from dba_tab_partit
ions where table_name='HASH_PARTITION';
PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------------------ ----------
1 SYS_P41 317
2 SYS_P42 304
3 SYS_P43 657
4 SYS_P44 591
5 SYS_P45 599
6 SYS_P46 653
7 SYS_P47 629
8 SYS_P48 646
9 SYS_P49 311
10 SYS_P50 292
11 SYS_P51
11 rows selected.
Opps no data in 11 partition. Let gather the stats.
SQL> exec dbms_stats.gather_table_stats('TEST','HASH_PARTITION');
PL/SQL procedure successfully completed.
SQL> select partition_position, partition_name, num_rows from dba_tab_partitions where table_name='HASH_PARTITION';
PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------------------ ----------
1 SYS_P41 317
2 SYS_P42 304
3 SYS_P43 317
4 SYS_P44 591
5 SYS_P45 599
6 SYS_P46 653
7 SYS_P47 629
8 SYS_P48 646
9 SYS_P49 311
10 SYS_P50 292
11 SYS_P51 340
Here we can see data from partition 3 is distributed to 11th partition.
SQL>alter table test.hash_partition add partition;
SQL> select partition_position, partition_name, num_rows from dba_tab_partit
ions where table_name='HASH_PARTITION';
PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------------------ ----------
1 SYS_P41 317
2 SYS_P42 304
3 SYS_P43 317
4 SYS_P44 306
5 SYS_P45 599
6 SYS_P46 653
7 SYS_P47 629
8 SYS_P48 646
9 SYS_P49 311
10 SYS_P50 292
11 SYS_P51 340
PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------------------ ----------
12 SYS_P53 285
Again data is took off from 4th partition to 12th partition.
SQL> alter table test.hash_partition add partition;
Table altered.
SQL> exec dbms_stats.gather_table_stats('TEST','HASH_PARTITION');
PL/SQL procedure successfully completed.
SQL> select partition_position, partition_name, num_rows from dba_tab_parti
ions where table_name='HASH_PARTITION';
PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------------------ ----------
1 SYS_P41 317
2 SYS_P42 304
3 SYS_P43 317
4 SYS_P44 306
5 SYS_P45 298
6 SYS_P46 653
7 SYS_P47 629
8 SYS_P48 646
9 SYS_P49 311
10 SYS_P50 292
11 SYS_P51 340
PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------------------ ----------
12 SYS_P53 285
13 SYS_P54 301
13 rows selected.
Same result.
Let me add 3 more partitions quickly just to see what happens when make 16 partition i.e making Power of 2 :)
SQL> alter table test.hash_partition add partition;
Table altered.
SQL> alter table test.hash_partition add partition;
Table altered.
SQL> alter table test.hash_partition add partition;
SQL> exec dbms_stats.gather_table_stats('TEST','HASH_PARTITION');
PL/SQL procedure successfully completed.
SQL> select partition_position, partition_name, num_rows from dba_tab_partit
ions where table_name='HASH_PARTITION';
PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------------------ ----------
1 SYS_P41 317
2 SYS_P42 304
3 SYS_P43 317
4 SYS_P44 306
5 SYS_P45 298
6 SYS_P46 343
7 SYS_P47 306
8 SYS_P48 341
9 SYS_P49 311
10 SYS_P50 292
11 SYS_P51 340
PARTITION_POSITION PARTITION_NAME NUM_ROWS
------------------ ------------------------------ ----------
12 SYS_P53 285
13 SYS_P54 301
14 SYS_P55 310
15 SYS_P56 323
16 SYS_P57 305
16 rows selected.
As expected now with Power of 2 we got equal sized partitions and this is what recommended by Oracle while making HASH Partitions we should give power of 2.
Thanks
Sources
http://docs.oracle.com/cd/B19306_01/server.102/b14223/parpart.htm
