Page 216 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Data Warehousing and Data Mining

notes bitmap filter and a bitmap index. First, bitmap filters are in-memory structures, thus eliminating
any index maintenance overhead due to data manipulation language (DML) operations made
to the underlying table. In addition, bitmap filters are very small and, unlike existing on-disk
indexes that typically depend on the size of the table on which they are built, bitmap filters can
be created dynamically with minimal impact on query processing time.

Comparing Bitmap Filtering with Optimized Bitmap Filtering

Bitmap filtering and optimized bitmap filtering are implemented in the query plan by using the
bitmap show plan operator. Bitmap filtering is applied only in parallel query plans in which
hash or merge joins are used. Optimized bitmap filtering is applicable only to parallel query
plans in which hash joins are used. In both cases, the bitmap filter is created on the build input
(the dimension table) side of a hash join; however, the actual filtering is typically done within the
Parallelism operator, which is on the probe input (the fact table) side of the hash join. When the
join is based on an integer column, the filter can be applied directly to the initial table or index scan
operation rather than the Parallelism operator. This technique is called in-row optimization.

When bitmap filtering is introduced in the query plan after optimization, query compilation time
is reduced; however, the query plans that the optimizer can consider are limited, and cardinality
and cost estimates are not taken into account.

Optimized bitmap filters have the following advantages:
1. Filtering from several dimension tables is supported.
2. Multiple filters can be applied to a single operator.
3. Optimized bitmap filters can be applied to more operator types. These include exchange
operators such as the Distribute Streams and Repartition Streams operators, table or index
scan operators, and filter operators.
4. Filtering is applicable to SELECT statements and the read-only operators used in INSERT,
UPDATE, DELETE, and MERGE statements.
5. Filtering is applicable to the creation of indexed views in the operators used to populate the
index.
6. The optimizer uses cardinality and cost estimates to determine if optimized bitmap filtering
is appropriate.

7. The optimizer can consider more plans.
How Optimized Bitmap Filtering is Implemented?

A bitmap filter is useful only if it is selective. The query optimizer determines when a optimized
bitmap filter is selective enough to be useful and to which operators the filter is applied. The
optimizer places the optimized bitmap filters on all branches of a star join and uses costing rules to
determine whether the plan provides the smallest estimated execution cost. When the optimized
bitmap filter is nonselective, the cost estimate is usually too high and the plan is discarded. When
considering where to place optimized bitmap filters in the plan, the optimizer looks for hash join
variants such as a right-deep stack of hash joins. Joins with dimension tables are implemented to
execute the likely most selective join first.
The operator in which the optimized bitmap filter is applied contains a bitmap predicate in the
form of PROBE([Opt_Bitmap1001], {[column_name]} [, ‘IN ROW’]). The bitmap predicate reports
on the following information:
1. The bitmap name that corresponds to the name introduced in the Bitmap operator. The
prefix ‘Opt_’ indicates an optimized bitmap filter is used.

210 LoveLy professionaL university