Partitioning gripes

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