Databases Reference
In-Depth Information
In contrast, the preferred mechanism for DML when operating with bitmap indexes is simply to
drop the bitmap indexes prior to a DML operation, or, if you have large, partitioned tables in the data
warehouse environment, mark the indexes for the given partitions(s) that are being loaded unusable
prior to the load.
The steps required to perform bitmap index maintenance in this regard are as follows:
1.
Drop all bitmap indexes, or, for a partitioned table, mark the bitmap indexes for the
targeted partitions for DML operations unusable.
2.
Perform the DML load operation.
3.
Rebuild all bitmap indexes, or, for a partitioned table, rebuild the bitmap indexes for the
affected partitions.
In the following example, follow these steps using the
BILLING_FACT
table and the same data as used
in the prior test. Use the 2 minutes and 19 seconds as a benchmark for comparison. The
BILLING_FACT
table is a partitioned table, and you are loading July 2011 data. You will therefore mark
all bitmap indexes unusable for the July partition.
Starting marking index partitions unusable at 20110915.2348.31
alter index billing_fact_bixfk102 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk103 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk104 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk105 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk106 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk107 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk108 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk109 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk110 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk111 modify partition billing_fact11_07p unusable;
alter index billing_fact_bixfk112 modify partition billing_fact11_07p unusable;
Completed marking index partitions unusable at 20110915.2348.56
The operation to mark the indexes unusable took a total of 25 seconds. Next, perform the identical
DML operation that took the 2 minutes and 19 seconds—only this time all affected partitions for the
bitmap indexes have been marked unusable and therefore the load operation doesn't build the index
entries for the new rows inserted.
SQL> INSERT INTO BILLING_FACT
2* SELECT * FROM BILLING_FACT_201107;
1000000 rows created.
Elapsed: 00:00:16.20
The load operation is obviously significantly faster without the bitmap indexes, taking only 16
seconds. Lastly, you need to rebuild the index partitions for the bitmap indexes that you marked
unusable in the first step.
