Rittman Mead - Delivered Intelligence

A little while back I took part in a partitioning survey. You may have seen it mentioned on Ton Kyte’s blog. It is certainly good to be asked these things - but I expect that the organiser of the survey already had an inkling of my views anyway.

Still, I actually like a lot about Oracle’s implementation of partitioning: it does not rely on distributing data between different processors (as some do) or only implementing just hash (or, perhaps, range) partitions as other vendors do; nor does it force DBA to define metadata partition templates before you create the tables. I really like the ability to choose between range, list or hash partitioning and to sub-partition partitions using a different scheme. Some of the partition schemes I would like to try aren’t available yet, but version 11 of the database is not too far away.

So what are my gripes:

• Well first off, it is not a free option, it costs masses of money on top of the Enterprise license. But to my mind it is essential functionality in any edition - partitioning is all about divide and conquer; with it you could reduce the size of data that need be read-write, reduce backup size, reduce IO in queries, make data lifecycle management as easy as dropping a partition. I did say could - you do need to know how to use partitioning to do these things; partition the wrong way and you may not gain much (or might even lose).
• The high value for a partition is stored in the data dictionary (DBA_TAB_PARTITITIONS) as a long - this makes it a fiddle to write generic partition maintenance code - for example splitting or joining partitions. We usually have to resort to encoding the partition key into the partition name, far from neat but workable.
• Partitions use some form of internal numbering starting from 1 - if you look in query plans that access a subset of partitions by literal you will see numbers in the PSTART & PEND columns of the plan. Dropping the first partition forces the renumber of all of the other partitions, a step that is often unnecessary for date range tables that only add partitions at the high end
• Truncating a partition rebuilds unusable indexes. That is so annoying - in a DW I disable bitmap indexes on a partition so that I can do an operation such truncate + insert (append) as quickly as possible. But if I truncate after I disable the index is silently rebuilt and that slows my insert operation (a lot)

But don’t get me wrong - partitions are perhaps better than the thing that sliced bread came second to in the bestness tables. Partition exchanging is brilliant and when a full scan only hits one partition out of a thousand just think how good you feel

Posted in Oracle Database |