Major New Undocumented Partitioning Feature in Oracle Database 11g Release 2
Follow @Oratweets
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 11.2.0.1 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.
Multidimensional Clustering (MDC) in Oracle Database: Can Exadata Beat This?
Leave a reply to stelladba Cancel reply
So Many Oracle Manuals, So Little Time 
Iggy,
That’s a terrific bit of news – I’ve been keen to see that for years.
I believe the rules of the TPC mean that the feature has to become publicly available with a few months. I hope the range option includes dates, I ‘ve been on a few sites where the perfect option would have been clusters partitioned by dates.
Well spotted.
Indeed a remarkable good news! Having a partitioned HC is a dream of all DB admins 🙂
Hmmm, would you use it in production (providing you are on 11.2.0.1 on Solaris Sparc)? How well tested do you think this TPC-inspired feature is?
Nice to file this away for use when it’s a bit better proven, mind.
Thanks, Jonathan. Patch 10374168 for Oracle Database 11.2.0.1 on 64-bit Solaris was released on April 13 and this probably meets the formal TPC requirements though I hope that the feature is eventually made available for all platforms in a documented manner.
For now, it’s a very limited feature and undocumented but, as Jonathan points out, this feature should have been introduced ten years ago so let’s hope that Oracle moves quickly to extend it to other forms of clustering and partitioning.
Well, a year on and the patch is still Solaris only. I have a situation that this would be perfect for but I am pretty hesitent (even though the environment is Solaris SPARC) given it seems to be benchmarkware. Poor form Oracle.