More on Interval Partitioning

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.