How optimized Bitmap Filtering is implemented?

A bitmap filter is beneficial only if it is selective. The query optimizer decides when an optimized bitmap filter is selective enough to be helpful and to which operators the filter is used. The optimizer field the optimized bitmap filters on all departments of a star join and uses costing rules to decide whether the plan supports the smallest estimated implementation cost.

When the optimized bitmap filter is nonselective, the cost estimate is generally too high and the plan is rejected. When considering where to locate optimized bitmap filters in the plan, the optimizer views for hash join variants include a right-deep stack of hash joins. Joins with dimension tables are implemented to execute the probable most selective join first.

The operator in which the optimized bitmap filter is used includes a bitmap predicate in the design of PROBE ([Opt_Bitmap1001], {[column_name]} [, ‘IN ROW’]). The bitmap predicate reports on the following data:

• The bitmap name correlated to the name introduced in the Bitmap operator. The prefix ‘Opt_’ denotes an optimized bitmap filter is utilized.

• The column probed opposite to. This is the term from which the filtered data flows through the tree.

• Whether the bitmap probe needs in-row optimization. When it is, the bitmap probe is invoked with the IN ROW parameter. Alternatively, this parameter is missing.

Optimized Bitmap Filtering Requirements

Optimized bitmap filtering has the following requirements −

• Fact tables are expected to have a minimum of 100 pages. The optimizer treated smaller tables to be dimension tables.

• There are only inner joins between a fact table and a dimension table are considered.

• The join predicate between the fact table and dimension table should be a single column join but does not require to be a primary-key-to-foreign-key relationship. An integer-based column is chosen.

• Joins with dimensions are only treated when the dimension input cardinalities are smaller than the input cardinality from the fact table.

Bitmap filtering and optimized bitmap filtering are executed in the query plan by using the bitmap show plan operator. Bitmap filtering is used only in parallel query plans in which hash or merge joins are used. Optimized bitmap filtering is relevant only to parallel query plans in which hash joins are used.

In both cases, the bitmap filter is generated on the build input (the dimension table) side of a hash join; but, the actual filtering is generally completed 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 used directly to the original table or index scan operation instead of the Parallelism operator. This technique is known as in-row optimization.