Databases Reference
In-Depth Information
will show the tables used in the join condition for bitmap join indexes, as well as the columns joined. See
the following example:
SQL> SELECT index_name, inner_table_name inner_table, inner_table_column inner_column,
2 outer_table_name outer_table, outer_table_column outer_column
3 FROM user_join_ind_columns
4* WHERE index_name = 'BR_FACT_BJIX002';
INDEX_NAME INNER_TABLE INNER_COLUMN OUTER_TABLE OUTER_COLUMN
--------------- --------------- ---------------- --------------- ----------------
BL_FACT_BJIX002 BILLING_FACT GEO_ID GEOGRAPHY_DIM GEO_ID
Summary
The bitmap and bitmap join indexes are most commonly used in the data warehouse environment.
The simple bitmap index can also be used outside of the data warehouse but the bitmap join index is
really specifically designed to be used within the star schema, which is a data model built for the data
warehouse environment.
They key advantages with bitmap indexes are that they can be created very quickly and generally
take up much less space than a B-tree counterpart index. This fact makes rebuild operations much more
attractive, as they can be rebuilt very quickly. Query performance is aided by bitmap indexes, as they can
be scanned quickly because they are smaller.
The biggest drawback of bitmap indexes is that DML operations can be much slower. If you use
bitmap indexes, the DML performance problems can be avoided simply by disabling or dropping the
indexes prior to the DML operation(s), and then enabling or rebuilding them after the DML operation is
complete.
The existence of bitmap join indexes within your star schema can aid query performance because
joined columns between the fact and dimension tables were stored at index creation time, which helps
query performance at execution time.
