Partitioning 12c

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 user 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.

Reminder:

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.

Update:

Excellent presentation from Maria Colgan, as usual. Had to have a word with her afterwards because she said at one point “… so the optimizer ignored your hint …”  (she didn’t really mean it though).

To the person who asked me afterwards about subverting the optimizer’s join order algorithm -  there is a hidden parameter which changes the choice of algorithm, but you might be better off trying to address the problem by disabling cartesian joins (_optimizer_cartesian_enabled = false); I haven’t done any experimentation with this, so it might not do what it’s name and description suggest; and it may, or may not, be one of those parameters that can be set with the /*+ opt_param() */ hint.

There’s also the parameter _optimizer_search_limit which defaults to 5. I don’t have a complete picture of what this parameter does, but in some tests I did about 10 years ago (9.2.0.4) one of its effects was to limit the number of cartesian joins that could appear in the examined join orders; so, possibly, if you set this to zero then you would end up with the initial join order that’s causing a problem. Again, it might be possible to set it up for the query with the /*+ opt_param() */ hint.

As ever, check with Oracle support before playing around with hidden parameters.