Databases Reference
In-Depth Information
See the rebuild syntax that follows and the associated time it took to rebuild the affected July 2011
partition for the 11 bitmap indexes:
Starting index rebuilds at 20110915.2318.28
alter index billing_fact_bixfk102 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk103 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk104 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk105 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk106 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk107 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk108 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk109 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk110 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk111 rebuild partition billing_fact11_07p;
alter index billing_fact_bixfk112 rebuild partition billing_fact11_07p;
Completed index rebuilds at 20110915.2318.53
It took 25 seconds to rebuild all the July partitions for your bitmap indexes. For partitioned tables,
you have the added flexibility of running many rebuilds at one time, which can also speed the overall
rebuild time. So, although more complex than performing a single DML insert operation on your fact
table, by splitting up the work into three pieces, the total time to insert one million rows took 1 minute
and 6 seconds, which is less than half the time as the straight insert statement. As stated, if you have
bitmap indexes on large partitioned tables, it is recommended to perform partition-level operations on
the indexes rather than completely drop the bitmap indexes. Even though bitmap indexes create very
quickly, if you have millions to billions of rows in a table, it can still take time. Furthermore, if you can
isolate DML operations to a given partition or set of partitions, you can simply mark the indexes
unusable before the DML operation begins and then rebuild only those partitions after the DML
operation is completed.
Alternatively, you can also issue the following command, which can save you from having to issue a
statement for each index:
alter table billing_fact modify partition billing_fact11_07p rebuild unusable local indexes;
The trade-off of the simplicity of this command is that the indexes for a given partition are built serially.
If you issue a command to rebuild a partition for each given index, you have the option to issue multiple
statements at once, which can speed index creation time because you are then essentially rebuilding the
indexes in parallel.
Understanding Bitmap Join Indexes
Bitmap join indexes, like normal bitmap indexes, are useful in data warehouse applications, specifically
with the star schema. One of the key benefits of a bitmap join index is implied in the name of the index:
the join. The basic premise of a bitmap join index is as follows: when creating a bitmap join index, which
is a join of indexed values between two tables, the join results are stored within the index itself. By doing
the join upfront and storing the results, it saves you from having to scan the indexes to get join values
between the two tables.
