Oracle Scratchpad

September 21, 2014

Partitioned Clusters

Filed under: 12c,clusters,Infrastructure,Oracle — Jonathan Lewis @ 7:28 pm BST Sep 21,2014

In case you hadn’t noticed it, partitioning has finally reached clusters in 12c – specifically They’re limited to hash clusters with range partitioning, but it may be enough to encourage more people to use the technology. Here’s a simple example of the syntax:

create cluster pt_hash_cluster (
        id              number(8,0),
        d_date          date,
        small_vc        varchar2(8),
        padding         varchar2(100)
-- single table
hashkeys 10000
hash is id
size 700
partition by range (d_date) (
        partition p2011Jan values less than (to_date('01-Feb-2011','dd-mon-yyyy')),
        partition p2011Feb values less than (to_date('01-Mar-2011','dd-mon-yyyy')),
        partition p2011Mar values less than (to_date('01-Apr-2011','dd-mon-yyyy'))

I’ve been waiting for them to appear ever since and the TPC-C benchmark that Oracle did with them – they’ve been a long time coming (check the partition dates – that gives you some idea of when I wrote this example).

Just to add choice (a.k.a. confusion) has also introduce attribute clustering so you can cluster data in single tables without creating clusters – but only while doing direct path loads or table moves. The performance intent is similar, though the technology and circumstances of use are different.


  1. Hi Jonathan,

    that’s great news — I’ve been waiting for partitioning support in clusters for a rather long time, too. Do you know if parallel inserts into hash clusters supported in 12c?

    Best regards,

    Comment by savvinov — September 21, 2014 @ 7:33 pm BST Sep 21,2014 | Reply

  2. I’ve ranted elsewhere on the subject of clusters, but I’ll briefly recap a few gripes here…

    I’m hoping someday we can partition clusters on some column other than cluster key columns. I’d dearly love to cluster together some transaction tables under the same synthetic PK column (an internal transaction ID) but have the whole thing partitioned by month (only stored in the main transaction row). The advent of reference partitioning made it sensible to even think in such terms.

    It would also be very useful to be able to compress cluster partitions. Of course that becomes tricky to think about, since that seems to imply either allowing direct-path insert(!) or maybe moving a cluster partition to compress it, or else licensing the Advanced Compression option to allow conventional-path inserts to be compressed.

    Almost all of the partitioning I do is to enable compression of historical data, with other goals like partition elimination being secondary. From what I read in the Oracle community, there’s more interest in partition elimination or partitionwise loading of data warehouses than enabling (basic) compression. So I might be rare in my opinions. But for me, I still can’t consider clusters even where I’d otherwise want to if it means giving up compression on my big tables.

    Comment by Jason Bucata — September 22, 2014 @ 5:38 am BST Sep 22,2014 | Reply

  3. Finally is here! I was really wondering when Oracle is going to make it official as after the TPC tests, there was an interim patch which they have used for their tests, but only for Solaris.

    Comment by nakinov — September 23, 2014 @ 10:41 am BST Sep 23,2014 | Reply

  4. […] I think that more or less wraps it up – but if you want to make life complicated you could start thinking about what goes on inside the index of an index cluster, and whether this introduces a special case of rowids; and this might get even more complicated when Oracle introduces the range-partitioned hash cluster. […]

    Pingback by Rowid | Oracle Scratchpad — October 20, 2019 @ 9:44 pm BST Oct 20,2019 | Reply

  5. […] Partitioned Hash Clusters 12c finally gets them […]

    Pingback by Hash Clusters – 2 | Oracle Scratchpad — July 6, 2020 @ 11:20 am BST Jul 6,2020 | Reply

  6. […] tpc-c test Oracle used a “future” feature of range partitioned hash clusters – which finally appeared in […]

    Pingback by Block Sizes | Oracle Scratchpad — July 6, 2020 @ 11:20 am BST Jul 6,2020 | 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.

Powered by