Oracle 11g Partitioning

Last time I mentioned that I have been helping Mark with some demonstrations of the Data Warehouse features of modern (notably 11g) Oracle databases. Having a few minutes spare I decided to take a look at partitioning.

I have long been a fan of partitioning or at least Oracle's implementation of it - the first time I used it was back in Oracle 8i with a 4.5 TB data warehouse running on a large chunk of iron with an massive number of disks, 4.5 TB is a large number of disks if the biggest disk drive you could get was 18GB!

I digress, if I was told that I could use only one feature in a data warehouse I would think long and hard about partitioning and bitmap indexes and probably side with partitioning; you may ask about parallel query, but that can be tricky to exploit well and there are too many pitfalls for the unwary.

Being an old hand with partitioning you get to know some of the quirks and develop techniques to handle them and to perform the routine tasks that a DBA should not have to do by hand; for example maintaining a rolling window of partitions (range on date) by dropping the oldest partition and creating a new one. You learn that it is sensible to encode the date into the partition name as that might be easier to process in your partition manipulation scripts than using the high_value stored in the xxx_TAB_PARTITIONS as that is a long and thus a bit more tricky to parse in a block of PL/SQL. You learn that for a partitioned index organized table you need to to look in xxx_IND_PARTITIONS

But when a new release of Oracle comes out with something called interval partitioning you sometimes dismiss it with a on off-the-cuff "oh, but that won't work for me as I need to know the name of the partition I am dropping and a system generated name won't cut it". Which is really another way of saying that you have not looked at the Oracle 11g SQL manual to see that there are new extensions to the partition maintenance ALTER TABLE commands and good old DELETE, UPDATE and INSERT. These extensions  now permit you to specify the partition by stating a value of the partition key rather than having to know the partition name: PARTITION FOR (TO_DATE('17-JAN-2007', 'dd-mon-rrrr')) is a much more elegant solution to storing the partition range value in it's name.

So what happens when you insert into a non existing partition on an interval partitioned table? Just what should happen; a new partition is created. And, interestingly, if there is a gap in your data it only creates the partitions needed for the data being inserted. And if more data arrives later to fill the gaps, the required missing partitions are created. This looks a pretty good feature to use, unless you use partition exchange; in that case you still need to create the partition before exchanging it.