Oracle Data Warehouse Tuning for 10g

72 3.2 Star Queries and Star Query Transformations

CREATE BITMAP INDEX xbj_sale_2

ON sale(time.time_id)

FROM sale, time

WHERE sale.time_id = time.time_id

NOLOGGING COMPUTE STATISTICS TABLESPACE indx;

DROP INDEX xbj_sale_3;

CREATE BITMAP INDEX xbj_sale_3

ON sale(product.product_id)

FROM sale, product

WHERE sale.product_id = product.product_id

NOLOGGING COMPUTE STATISTICS TABLESPACE indx;

DROP INDEX xbj_sale_4;

CREATE BITMAP INDEX xbj_sale_4

ON sale(industry.industry_id)

FROM sale, industry

WHERE sale.industry_id = industry.industry_id

NOLOGGING COMPUTE STATISTICS TABLESPACE indx;

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

transformation

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

Transformations

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

transformation

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.