72 3.2 Star Queries and Star Query Transformations
ON sale(time.time_id)
FROM sale, time
WHERE sale.time_id = time.time_id
DROP INDEX xbj_sale_3;
ON sale(product.product_id)
FROM sale, product
WHERE sale.product_id = product.product_id
DROP INDEX xbj_sale_4;
ON sale(industry.industry_id)
FROM sale, industry
WHERE sale.industry_id = industry.industry_id
Using four single column bitmap indexes for the bitmap join produces
the same result as for Figures 3.13 and 3.14. In Figure 3.15 the single join
index between the SALE fact and LOCATION dimension tables executes
slightly faster than the composite bitmap join index used in Figure 3.14.
Figure 3.14
A star
query using a
composite bitmap
join index.
3.2 Star Queries and Star Query Transformations 73
Chapter 3
3.2.3 Problems with Star Queries and Star
The problem is generally not with star queries and star transformations, but
more often than not it is related to a misunderstanding of bitmap indexing.
When deciding whether to use bitmap indexes or not, there are numerous
factors to take into account:
Converting a BTree index to a bitmap index because a BTree is prob-
lematic could quite possibly make your problem worse.
Bitmap indexes will build faster than BTree indexes since they occupy
less physical space. The down side to this is that any type of DML
activity will involve access to more index rows for a bitmap than a
BTree, since a bitmap is far more compressed.
One very large factor affecting bitmap index size and, thus, perfor-
mance and possible optimizer use is data distribution. A bitmap
index is likely to be much more effective if like key values are contig-
uous. In other words, your data is physically ordered in the order
required by the bitmap index.
Figure 3.15
A star
query using
multiple single
column bitmaps to
create a bitmap
join index.
74 3.2 Star Queries and Star Query Transformations
Note: The queries executed in figures shown so far in this chapter show no
performance improvement using bitmap indexes and star queries. Data in
the generated data warehouse database used for this book is random, and
contiguity of data could be an issue for bitmap indexing.
Bitmap indexes should only be created on individual nonunique col-
umns. Do not create multiple column composite indexes using bit-
maps. If multiple columns are required, simply create separate single-
column bitmap indexes for each column. The optimizer can then use
a WHERE clause in any sequence and still use all of the single col-
umn bitmap indexes.
Overflow and contention have in the past been serious problems with
bitmap indexes and can cause catastrophic performance problems
over extended periods of time and continuous changes to data. The
structure of a bitmap index is fixed and not changeable by data
changes. Any additions or alterations could result in overflow. A data-
base overflow is where parts of the same thing are split into separate
areas of a storage device such as a disk. This can increase I/O and cen-
tral processing unit (CPU) costs dramatically.
When doing any kind of data warehouse batch updates it might be
best to drop bitmap indexes before executing the batch, and then
regenerating the bitmap indexes afterwards. This is particularly rele-
vant in versions of Oracle Database prior to 10g. If data warehouse
size and time factors for bitmap index regeneration prohibit this pru-
dent practice, it might be best to avoid bitmap indexing altogether,
even in a data warehouse. Benefit must always outweigh cost. The
real benefit of multiple single column bitmap indexes is the unpre-
dictability of filtering sequences, so important to BTree indexes in
older versions of Oracle Database. Additionally, the advent of new
bells and whistles in recent versions of Oracle Database, such as
BTree index fast full scans, skip scans, and less reliance on matching
WHERE clause sequences with BTree indexes all make bitmap
indexes less useful, even multiple single column bitmap indexes.
Bitmap index cardinality is a relative term and not an absolute. In
other words 10,000, distinct dimensional values as compared to 5 bil-
lion fact rows is likely to be relatively low cardinality. Similarly, two
values of M for male and F for female contains only two distinct val-
ues, but if the facts are small in number there is no point in creating a
bitmap just because there are only two values.

Get Oracle Data Warehouse Tuning for 10g now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.