| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Oracle Bitmapped Indexes
select 'spool off;' from dual;
spool off;
set termout on;
@idx1
!sort idx2.lst
Listing 8.13 shows the listing from bitmap.sql, and displays the
indexes according to the number of distinct keys.
Listing 8.13 The results of bitmap.sql.
3 is the cardinality of Index GEO_LOC_PK on column GEO_LOC_TY_CD of
table DON.GEM_LCC
4 is the cardinality of Index REGION_IDX on column REHION of table
DON.CUSTOMER
7 is the cardinality of Index GM_LCK on column GEO_LC_TCD of table
DON.GEM_LCC
8 is the cardinality of Index USR_IDX on column USR_CD of table
DON.CUSTOMER
50 is the cardinality of Index STATE_IDX on column STATE_ABBR of
table DON.CUSTOMER
3117 is the cardinality of Index ZIP_IDX on column ZIP_CD of table
DON.GEM_LCC
71,513 is the cardinality of Index GEO_LOC_PK on column GEO_LOC_CD
of table DON.GEM_LCC
83,459 is the cardinality of Index GEO_KEY_PK on column
GEO_LOC_TY_CD of table DON.GEM_LCC
Of course, columns such as GENDER and REGION should be made into
bitmaps--but what about STATE with 50 values, or area_code with a
few hundred values? Intuition tells us that the benefits from
bitmapped indexes are a function of the cardinality and the number
of rows in the table, but no hard and fast rule exists for
identifying what type of index is always best. An heuristic approach
is called for, and it is relatively easy for the Remote DBA to create a
b-tree index, run a timed query, re-create the index with the BITMAP
option, and re-execute the timed query.
Regardless, bitmapped indexes are critical to the performance of
decision support system, especially those that are queried in an
ad-hoc fashion against hundreds of column values. See Chapter 10,
Tuning Oracle Data Warehouse And OLAP Applications, for a discussion
of the applications of bitmapped indexes.
|
