Major New Undocumented Partitioning Feature in Oracle Database 11g Release 2
Finding a major new undocumented Oracle feature hiding in plain sight is not as exciting as finding a previously unknown Picasso sketch but it’s still interesting. While doing the research my new book on Oracle indexing, I was reading the full-disclosure report for Oracle’s latest record-shattering TPC-C benchmark when I noticed something that I had not seen in previous benchmark reports. Oracle uses hash clusters effectively in its TPC-C benchmarks for the quickest possible access when the primary key is specified. However—until now—Oracle has never seen fit to provide partitioning capabilities for hash clusters or for indexed clusters either.
The full disclosure report shows that hash clusters can now be partitioned by ranges. My sources at Oracle tell me that range-partitioned hash clusters are only available in Oracle Database 220.127.116.11 on Solaris Sparc with additional patch 10374168 titled “PATCH FOR ORACLE 30M TPCC CLUSTER BENCHMARK.” Clustering is as important as indexing and I hope that Oracle will provide additional partitioning options for clusters in future releases.
Here is an extract from page 175 of the full-disclosure report.
create cluster custcluster ( c_id number ,c_d_id number ,c_w_id number ) single table hashkeys 2700000000 hash is ((c_w_id * 30000 + c_id * 10 + c_d_id - 30011)) size 850 pctfree 0 initrans 3 storage ( buffer_pool recycle ) parallel ( degree 1024 ) partition by range( c_w_id ) ( partition cust_0 values less than ( 90001 ) tablespace cust_0 ,partition cust_1 values less than ( 180001 ) tablespace cust_1 ,partition cust_2 values less than ( 270001 ) tablespace cust_2 ,partition cust_3 values less than ( 360001 ) tablespace cust_3 ,partition cust_4 values less than ( 450001 ) tablespace cust_4 ,partition cust_5 values less than ( 540001 ) tablespace cust_5 ,partition cust_6 values less than ( 630001 ) tablespace cust_6 ,partition cust_7 values less than ( 720001 ) tablespace cust_7 ,partition cust_8 values less than ( 810001 ) tablespace cust_8 ,partition cust_9 values less than ( 900001 ) tablespace cust_9 ,partition cust_10 values less than ( 990001 ) tablespace cust_10 ,partition cust_11 values less than ( 1080001 ) tablespace cust_11 ,partition cust_12 values less than ( 1170001 ) tablespace cust_12 ,partition cust_13 values less than ( 1260001 ) tablespace cust_13 ,partition cust_14 values less than ( 1350001 ) tablespace cust_14 ,partition cust_15 values less than ( 1440001 ) tablespace cust_15 ,partition cust_16 values less than ( 1530001 ) tablespace cust_16 ,partition cust_17 values less than ( 1620001 ) tablespace cust_17 ,partition cust_18 values less than ( 1710001 ) tablespace cust_18 ,partition cust_19 values less than ( 1800001 ) tablespace cust_19 ,partition cust_20 values less than ( 1890001 ) tablespace cust_20 ,partition cust_21 values less than ( 1980001 ) tablespace cust_21 ,partition cust_22 values less than ( 2070001 ) tablespace cust_22 ,partition cust_23 values less than ( 2160001 ) tablespace cust_23 ,partition cust_24 values less than ( 2250001 ) tablespace cust_24 ,partition cust_25 values less than ( 2340001 ) tablespace cust_25 ,partition cust_26 values less than ( maxvalue ) tablespace cust_26 ) ;
In previous TPC-C benchmarks, the number of hash keys for the CUSTCLUSTER hash cluster was set to the total number of customers in the database (number of warehouses * 10 sales districts per warehouse * 3000 customer per sales district). In the latest TPC-C benchmark, the number of hash keys for CUSTCLUSTER is set to the number of customers per partition (90,000 warehouses * 10 * 3000). This does make sense though.