I made a mistake a few days ago following up a question on the OTN database forum. The question was about a problem creating a hash/list composite partitioned table, and one of the respondants suggested that perhaps the problem appeared because hash/list wasn’t a legal combination.
Spot on: so I confirmed that observation and supplied a link to the official Oracle white paper that listed the combinations that were legal in 11.2 for composite partitioning. In fact, although I was fairly sure that hash/list wasn’t legal, I had even run up a quick test to check that the attempt would fail before I’d searched online for the document.
Fortunately other people had different ideas about the original failure, and one of them supplied a link to a thread on AskTom which actually included some SQL to create a hash/list composite partitioned table. Naturally I tested it (even though it was from Tom Kyte) and obviously it worked (after all it was from Tom Kyte) – and then I spotted the syntax error in the example I had created for myself.
Trust but verify … and then …
I had fallen into two traps – and one of them was documented in my own “Trust” posting from 2006.
The white paper was dated September 2009 (Tom’s example was dated June 2013) and as it says in my Trust note:
- If its date is more than about 18 months old – don’t assume it’s (still) true
- If it’s not your exact version number – don’t assume it’s (still) true
The second trap was an example of confirmation bias, I was fairly sure that my test was supposed to fail with Oracle error “ORA-00922: missing or invalid option”, so when it failed with exactly that error I didn’t check why it had failed and didn’t notice that I had swapped the order of a couple of clauses in the create table statement. It’s very easy to think you’ve done enough when testing – especially when your test results match your expectation.
Update – later that day
So I’ve had an email asking me how I got the ORA-00922. Here’s the SQL as I wrote it – don’t give the answer in the comments, but how quickly can you spot what I did wrong ?
create table t1 ( object_name, object_type, created ) partition by hash(object_name) partitions 4 subpartition by list(object_type) subpartition template ( subpartition sp1 values ('TABLE'), subpartition sp2 values ('INDEX'), subpartition sp3 values (default) ) as select object_name, object_type, created from all_objects ;
It’s a deceptive error (to me, at least) because even though I know it’s wrong it still looks right.