Another option for partitioning index-organized tables is to use the hash method. In Example 4-23, the
sales index-organized table is partitioned by the hash method.
A well-designed hash function is intended to distribute rows in a well-balanced fashion among the partitions. Therefore, updating the primary key column(s) of a row is very likely to move that row to a different partition. Oracle recommends that you explicitly specify the
ENABLE ROW MOVEMENT clause when creating a hash partitioned index-organized table with a changeable partitioning key. The default is that
ENABLE ROW MOVEMENT is disabled.
Example 4-23 Creating a hash partitioned index-organized table
CREATE TABLE sales(acct_no NUMBER(5), acct_name CHAR(30), amount_of_sale NUMBER(6), week_no INTEGER, sale_details VARCHAR2(1000), PRIMARY KEY (acct_no, acct_name, week_no)) ORGANIZATION INDEX INCLUDING week_no OVERFLOW PARTITION BY HASH (week_no) PARTITIONS 16 STORE IN (ts1, ts2, ts3, ts4) OVERFLOW STORE IN (ts3, ts6, ts9);