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:

rem
rem     Script:         cluster_partition.sql
rem     Dated:          July 2011 / Sept 2014
rem     Author:         J.P.Lewis
rem

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 used them for – 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 simple heap 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.

9 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

  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

  7. It seems add, drop and truncate options are not provided for clusters (“ORA-02230: invalid ALTER CLUSTER option”, and indeed there’s no mention of them in the documentation). I’m struggling a bit to see a use case where you would have a table large enough to need partitioning but with fixed partitions. All the large partitioned tables I ever work with are partitioned by business date, with new dates added and old ones purged routinely.

    Comment by William Robertson — November 10, 2020 @ 4:57 pm GMT Nov 10,2020 | Reply

    • Oops, I meant add, drop and truncate *partition* options are not provided for clusters.

      Comment by William Robertson — November 10, 2020 @ 5:25 pm GMT Nov 10,2020 | Reply

    • William,

      Thanks for the comment – I’ve delayed answering because I put a shout out on twitter to see if any of my followers had been using the technology. No answers.

      I have to say that there have been only a couple of times in my career that I’ve been at a customer site wishing that the 11.2 tpc-c benchmark had been extended to a genuine product feature – but I no longer remember what the clients’ businesses were that made them see so appropriate. Presumably some steady volume of data per time period that needed clustering and discarding with time.

      I’ve been poking at 19c partitioned clusters to see what they can do – and, as you say, there’s a lot missing that needs to be implemented.

      Here’s a comment I made on the OTN (as it was) forum in 2017 – and things don’t seem to have changed.

      Interestingly the basic support for the feature is far from complete – even in 12.2.0.1: after creating your cluster trying querying user_clusters to find it – it isn’t there; and the dba_segments information is wrong: sum(blocks) from dba_segments = 2 * space used from tablespace, and in 12.1.0.2 a call to dbms_metatdata doesn’t even find the partitioning clauses.

      I’m not sure I’d want to use feature without being able to put some pressure on Oracle to give better support (i.e. I’d want to be working for an important customer paying lots of money to Oracle).

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — November 17, 2020 @ 7:47 pm GMT Nov 17,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:

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 )

Connecting to %s

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

Website Powered by WordPress.com.

%d bloggers like this: