Databases Reference
In-Depth Information
What the following example demonstrates is that it is actually much faster to simply drop and
recreate bitmap indexes before and after a load operation. In the following example, you have a star
schema with a central fact table, with dimension tables surrounding the fact table. In order to achieve
star transformation, at minimum you need foreign keys on the fact tables pointing back to the parent
dimension primary keys. In this example, you have a total of 11 bitmap indexes on the fact table that are
created on the foreign key columns from the dimension tables. There is also a primary key index and two
B-tree indexes. Columns with date-based datatypes are not good candidates for bitmap indexes because
of their cardinality, so for date-based columns, a normal B-tree index is recommended.
The following query shows your indexes:
SQL> select index_name, index_type, partitioned
2 from user_indexes
3* where table_name = 'BILLING_FACT';
INDEX_NAME INDEX_TYPE PAR
------------------------------ --------------------------- ---
BILLING_FACT_PK NORMAL YES
BILLING_FACT_IXFK01 NORMAL YES
BILLING_FACT_BIXFK102 BITMAP YES
BILLING_FACT_BIXFK103 BITMAP YES
BILLING_FACT_BIXFK104 BITMAP YES
BILLING_FACT_BIXFD105 BITMAP YES
BILLING_FACT_BIXFK106 BITMAP YES
BILLING_FACT_BIXFK107 BITMAP YES
BILLING_FACT_BIXFK108 BITMAP YES
BILLING_FACT_BIXFK109 BITMAP YES
BILLING_FACT_BIXFK110 BITMAP YES
BILLING_FACT_BIXFK111 BITMAP YES
BILLING_FACT_BIXFK112 BITMAP YES
BILLING_FACT_IX01 NORMAL YES
14 rows selected.
It is very common to have many bitmap indexes on a fact table, and the number increases as the
number of dimension tables increases in a star schema. It is cases like this where performance suffers
with DML operations on tables with a large number of bitmap indexes. This degradation occurs, of
course, in a typical star schema configured for star transformation.
For demonstration purposes, insert one million rows into the fact table. See the following DML
operation and note the time it takes to complete the operation:
SQL> INSERT INTO BILLING_FACT
2 *SELECT * FROM BILLING_FACT_201107;
1000000 rows created.
Elapsed: 00:02:19.29
You can see that it took 2 minutes and 19 seconds to insert the rows into your fact table. At a glance,
the time taken appears reasonable. Keep in mind that in the data warehouse environment, it is common
to process hundreds of millions of rows a day. If you extrapolated this number to 100 million rows, it
could take over 2 hours to load 100 million rows.
