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 12.1.0.2. 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 11.2.0.1 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) 12.1.0.2 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.

3 Comments »

  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,
    Nikolay

    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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog at WordPress.com.