|
|
|
|
Oracle Bitmapped Indexes
Oracle Tips by Burleson Consulting
|
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Oracle Bitmapped Indexes
Bitmaps are especially important for data warehouse/decision support
systems, where ad-hoc, unanticipated queries make it impractical for
the Oracle Remote DBA to index on all possible combinations of columns.
Assume that a manager wants to know the average income for all
college-educated customers who drive red or blue cars in Wyoming or
Nevada. Furthermore, assume that there are 1 million rows in the
customer table. The following query would be very hard to service
using traditional indexing:
select avg(yearly_income)
from customer
SEE CODE DEPOT FOR FULL SCRIPT
state_residence in (‘WY’,’NV)
order by avg(yearly_income);
In a bitmapped index, it is not necessary to read all 1 million rows
in the customer table. Instead, the query manager will build row-id
lists for all “1” values for education, car_color, and
state_residence, and then match up the row-ids for those that appear
in all three columns. When the query is ready to access the rows, it
already has a list of row-ids for all rows that meet the selection
criteria.
To understand bitmapped indexes, imagine a very wide, fat table with
only a few rows. In a bitmapped index, each unique value has one
row, such that our REGION index contains only four rows. Across the
bitmap, each row in the base table is represented by a column, with
a “1” in the bitmap array if the value is true, and a “0” if it is
false. Because of the high amount of repeating ones and zeros,
bitmapped indexes can be very effectively compressed and expanded at
runtime. In fact, the lower the cardinality, the better the
compression, such that we can expect a higher compression of a
GENDER index with two distinct values than with a STATE index with
50 distinct values. Uncompressed, the STATE index would be 48 times
larger than the GENDER bitmap, because one row in the bitmap array
is required for each unique value.
This is an excerpt from "High Performance
Data Warehousing".
|
If you
like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference , with
over 900 pages of BC's favorite tuning tips &
scripts.
You can buy it
directly from the publisher and save 30%, and get
instant access to the code depot of Oracle tuning
scripts. |
|
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
|
|
|
|
|