Oracle Scratchpad

August 19, 2015


Filed under: Infrastructure,Oracle,Partitioning — Jonathan Lewis @ 10:02 am BST Aug 19,2015

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 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 ?

rem     Script:         pt_hash_list.sql
rem     Author:         J P Lewis
rem     Dated:          August 2015
rem     Last tested

create table t1
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)
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 as if it ought to be right.


  1. And, of course, there are always exceptions…

    In the case of XMLType content combined with XML (domain) inde

    – equipartitioning (REF?) for XMLType OR storage is supported
    >= 11.2.x
    – range partitioning and list partitioning is supported
    – composite is not supported
    >= 12.1.x
    – hash partitioning is supported
    – interval partitioning is supported for use with structured XMLIndex
    – composite? not sure…

    Interval partitioning is not (yet?) supported for use with Unstructured XMLIndex

    In all cases, it is best to verify the partitioning options and restrictions regarding XML(type) content and XMLIndex domain indexes via Oracle Support…



    Comment by Marco Gralike — August 19, 2015 @ 10:23 am BST Aug 19,2015 | Reply

    • Marco,

      Thanks for that – I may have to add another caveat to the Trust note:

      • If there’s a rational justification and repeatable test case but the feature set doesn’t match the one you’re using – don’t assume it’s true.

      Comment by Jonathan Lewis — August 19, 2015 @ 10:30 am BST Aug 19,2015 | Reply

  2. Apart from the content, this “Oops” thread provides insight that the best among us ‘fess up as soon as they know they’ve made a mistake and go the extra mile to correct the record clearly. That characteristic is a great feature to have in the pursuit of the truth of things. Bravo, JL, even in a mistake you’ve made a triumph of the correction!

    Comment by rsiz — August 19, 2015 @ 7:33 pm BST Aug 19,2015 | Reply

  3. Well,
    on the ORA-922 I had to double, or actually triple, check the docs for the syntax and still not sure what the error was. In the end I had to try the code, both the one the with the error and the one I though it would be right, to figure it out and I was right, so reading the docs is worth the effort.

    So read the docs and test would be my approach.

    Comment by Jose Rodriguez — August 21, 2015 @ 8:44 am BST Aug 21,2015 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by