Oracle Scratchpad

October 2, 2012

Partitioning 12c

Filed under: 12c,Oracle,Partitioning — Jonathan Lewis @ 8:11 pm BST Oct 2,2012

Most useful presentation of OOW so far, from Hermann Baer of Oracle on improvements in partitioning features in 12c – and there are lots of useful ones, including:

Online move of a partition – so easy to compress a partition when it has reached its final “read-only” state

multiple partition maintenance in one operation – e.g. merge 3 monthly partitions into one quarterly partition, or split one partition into 24 (think about “how do I partition a non-partitioned table”, and 12c has just made it easier and quicker – exchange it into an empty partitioned table, then do one massive split).

partial indexing – define which partitions of a partitioned table should be included in the indexes you create on the table – and the optimizer also knows that different partitions need different plans (an enhancement of “table expansion”.

interval-reference partitionining – you can create an interval partitioned table, and use ref-partitioning to create child (and further decendent) tables, and their partitions will automatically be generated, truncated, and dropped as the parent is extended, truncated or dropped (needs enabled foreign key constraints).

Lots more details – and lots of stress-testing to be done – but I’m off to hear “the optimizer lady” talk about hints.


Oracle has a “safe harbour” slide at the start of all presentations about future developments pointing out that the information presented is an indication of direction, but not guaranteed to make it into production.


  1. Wow !!!

    Comment by Asif Momen — October 3, 2012 @ 4:37 am BST Oct 3,2012 | Reply

  2. Hmmm…. Given the largest sections of the SQL manual already are by far the CREATE and ALTER TABLE ones, I shudder at the thought of what this is going to do to them…
    Still: very cool stuff, indeed! And most welcome.

    Comment by Noons — October 3, 2012 @ 5:13 am BST Oct 3,2012 | Reply

  3. I like new features and some of partitioning enhacements seems to be usefull. However we will not upgrade to 12c sooner than in 4 years.
    We have upgraded our largest databaese (corebanking system application, 4500 users) recently ( -> 11g has been relased 5 years ago (11gR2 3yeaes). I’ve filed with my colleague 8 SR! 4 of them have quite big negative impact on business

    – very silly new SQL locking mechanism with FK on tables in RO tablespaces (introeuced in 10gr2, thank to God it is possible to switch it off)
    – huge allocations in KGLH0 heaps and KKSSP even (shared pool)
    – many shared pool issues, BTW oracle was forced tu disable shared pool durations in because of several bugs
    – issue with select for update on table with FBI (also mentioned here on your blog)
    – dataguard (max avsilability) issue, LNS process (primary) and RFS (standby) communication use to hang resulting into 30seconds “whole database commit hang” which is terrible for OLTP system. servers, disk subsystem and network are OK..
    – index SS access path costed too low (index cost formula consider CLUF for index SS in same way as for index RS)
    – flashback database dos not work with standby and TTS (unless tts are switched RO->RW->RO)

    Pavol Babel

    Comment by Pavol Babel — October 3, 2012 @ 8:07 am BST Oct 3,2012 | Reply

  4. I would certainly agree that the ‘move online’ feature for partitions is a great improvement of Oracle Database 12c!

    Comment by Uwe Hesse — October 4, 2012 @ 6:55 am BST Oct 4,2012 | Reply

    • Uwe,

      It’s also interesting to consider that if Oracle doesn’t make this a feature of non-partitioned tables you could always turn every (non-clustered)heap table into a single partition partitioned table simply so that you can move the partition online. Simply create an empty partitioned cloned of the original table, then exchange partitions. (Only suitable as a strategy if you have already paid for the partitioning option, of course.)

      Comment by Jonathan Lewis — October 4, 2012 @ 2:29 pm BST Oct 4,2012 | Reply

  5. […] little detail that Hermann Baer mentioned in his presentation yesterday was the ability to create multiple indexes with the same column definition – something which […]

    Pingback by Indexing 12c « Oracle Scratchpad — October 4, 2012 @ 2:48 pm BST Oct 4,2012 | Reply

  6. I remember the old DSI optimizer material saying that _optimizer_search_limit = 5 means that CBO will evaluate up to 5 factorial (120) join permutations which may include cartesian joins and from then onwards only non-cartesian join orders (up to optimizer_max_permutations) per query block.

    Comment by Tanel Poder — October 5, 2012 @ 4:42 am BST Oct 5,2012 | Reply

  7. […] Jonathan Lewis – Indexing 12c and Partitioning 12c […]

    Pingback by Oracle Database 12c and its features… « musingdba — October 18, 2012 @ 7:21 pm BST Oct 18,2012 | 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