More on Interval Partitioning
August 7th, 2010 by Peter Scott
As I mentioned in the past, I am a great fan of Oracle bitmap indexes; they allow the database to do some really good optimizations and reduce the impact of typical data warehouse queries that need to filter and fetch large chunks of fact data. The flip side to their success in queries is that they can slow the ETL data load process as we update or create individual index entries that refer to many rows of data. The traditional way to deal with this is set the index to be unusable before loading and rebuild it after the ETL completes; for partitioned indexes (and if the indexed table is partitioned then any bitmap indexes must be locally partitioned) you can do this a partition level and just rebuild the unusable partitions.
Typically, I set indexes to be unusable by issuing an ALTER INDEX my_bitmap_index UNUSABLE and for partitioned tables ALTER INDEX my_bitmap_index PARTITION aug_10_part UNUSABLE, here I use a “smart partition name” that allows me to programatically determine the correct index partitions to manipulate – using the HIGH_VALUE of the partition from DBA_IND_PARTITIONS is problematic as it is a LONG data type and thus a tad convoluted to query (see Adrian Billington’s note on working with LONG columns).
However, when we use Oracle 11g interval partitioning the partition names are system generated; so how do we find the index’s partitions that need to be set unusable? There is no “PARTITION FOR” construct to alter an index’s partitions to be unusable, and the name of partition is not inherited from the table -it gets its own system generated name. There is still a way to do this and assuming that my_table is range partitioned by DATE interval; ALTER TABLE my_table MODIFY PARTITION FOR (TO_DATE(’1-Aug-2010′,’dd-mon-yyyy’)) UNUSABLE LOCAL INDEXES
Before interval partitions I have rebuilt unusable index partitions by writing a procedure to loop through all of the index partitions marked as unusable in the data dictionary and issue an ALTER INDEX …. REBUILD PARTITION command – here I can easily find the partition name to rebuild as the unusable marker is not held in a LONG, there is also an ORACLE package (DBMS_PCLXUTIL) that can do this; now, with Oracle 11g maybe the simplest thing though is to use ALTER TABLE my_table MODIFY PARTITION FOR (TO_DATE(’1-Aug-2010′,’dd-mon-yyyy’)) REBUILD UNUSABLE LOCAL INDEXES.
September 9th, 2010 at 10:14 am
Hi,
A small note on PEL and partitions. The Exadata best practice documentation currently recommends a default Partition size of 8mb (by setting CELL_PARTITION_LARGE_EXTENTS to TRUE) when creating partitioned tables.
Now if you have a warehouse like ours and you build your partitions based on a date index (row insert date for example to coincide with a nightly load), and you have a calendar table to control the dates in the database then you need to consider the amount of data you are creating.
Our calendar table runs from start 2008 to end 2019 so is 12 years, or 4380 days/rows. This means that for all the fact tables we have PEL enabled on we created 8mb partitions going forwards for every day left in the calendar!
Our database went from about 200gb to 3TB when we implemented PEL which had implications on storage, backup etc. We have subsequently scaled back the partion size to something more manageable, but please beware the “best practice” is not necessarily the best option.
Cheers,
Matt.
September 9th, 2010 at 8:12 pm
@Matt
PEL and interval partitions is an interesting concept – on the face of it you can do it as the partition does not yet exist :-) A way round this is to insert a dummy row into the partition to cause it to be generated and then do the exchange.
This approach may get around (or really hold off until later) the problem with a huge table of ‘empty’ partitions. Interval partitions -you don’t need to pre-create the partitions until just before you need to do the PEL
September 16th, 2010 at 12:09 pm
hmm… thats interesting – I shall read further!