| |
 |
|
Oracle 8 Tips
by Burleson Consulting |
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Oracle Bitmapped Indexes
Prior to release 7.3 of Oracle, it was never recommended that the
Remote DBA create an index on any fields that were not “selective” and had
less than 50 unique values. Imagine, for example, how a traditional
b-tree index would appear if a column such as REGION were indexed.
With only four distinct values in the index, the SQL optimizer would
rarely determine that an index scan would speed up a query;
consequently, the index would never be accessed. Of course, the only
alternative would be to invoke a costly full-table scan of the
table. Today, we are able to use bitmapped indexes for low
cardinality indexes. Cardinality is defined as the number of
distinct key values expressed as a percentage of the number of rows
in the table. Hence, a million row index with four distinct values
has a low cardinality while a 100 row table with 80 distinct values
has a high cardinality.
It is interesting to note that bitmapped indexes have been used in
commercial databases since Model 204 was introduced in the late
1960s. However, their usefulness had been ignored until the data
warehouse explosion of 1994 made it evident that a new approach to
indexing was needed to resolve complex queries against very large
tables.
Bitmapped indexes are a new feature of Oracle 7.3 that allow for
very fast Boolean operations against low cardinality indexes.
Complex AND and OR logic is performed entirely within the index--the
base table need never be accessed. Without a bitmapped index, some
decision support queries would be impossible to service without a
full-table scan.
|
