Compound AND Conditions in Boolean Predicates
Oracle Tips by Burleson

We also see specialized cost-based tuning optimization techniques for compound AND conditions in a SQL query. Prior to the introduction of bitmap indexes, a concatenated index could be used when a query had multiple AND conditions.

For example, assume the following bitmap indexes on the emp table:

create bitmap index
   dept_bit
on
   emp
   (deptno);

create bitmap index
   job_bit
on
   emp
   (job);

Now, let’s look at another query. Here we filter the result rows where job=salesman and deptno=30 and sal > 1000:

select /*+ first_rows */
   ename,
   job,
   deptno,
   mgr
from
   emp
where
   job = 'SALESMAN'
and
   deptno = 30
and
   sal > 1000
;

Here is the execution plan when using B-tree indexes on the job, dept, and sal columns. Note that the CBO chooses the most selective of the three indexes and uses that index:

Now, we drop the B-tree indexes and replace them with three bitmap indexes.

It makes sense to have the deptno index as a bitmap index because there are only 10 distinct departments. It also makes sense to make the job column a bitmap index because there are only 15 distinct job titles. In the real world, it does not make sense to make sal a bitmapped index, because it is a scalar numeric value, but we have done it here purely for illustrative purposes. Note the change to the execution plan after changing the index structures. Next we see that the dept column is still used, but we invoke the BITMAP CONVERSION execution method.

Now, because we have bitmap indexes, we can further improve the speed of this query by using the index_combine hint to our query. The index_combine hint is used to force a bitmap access path for the table. If no indexes are given as arguments for the index_combine hint, the optimizer will choose whatever Boolean combination of bitmap indexes has the best costing estimate for the table access.

The index_combine hint directs the optimizer to perform a ROWID intersection operation from both bitmaps. In practice, it is always a good idea to specify the table name and both index names in the hint. Here is the same query with the index_combine hint, specifying all three bitmap indexes. For this example, we created a dept_bit and job_bit index on dept and job columns.

select /*+ index_combine(emp, emp_deptno_bit, emp_job_bit, emp_sal_bit) */
   ename,
   job,
   deptno,
   mgr
from
   emp
where
   job = 'SALESMAN'
and
   deptno = 30
and
   sal > 1000
;

Here is the new execution plan:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     4
TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
    BITMAP CONVERSION
TO ROWIDS                                                            1
      BITMAP AND
                                                                     1
        BITMAP INDEX
SINGLE VALUE                   EMP_DEPTNO_BIT                        1
        BITMAP INDEX
SINGLE VALUE                   EMP_JOB_BIT                           2
        BITMAP MERGE
                                                                     3
          BITMAP INDEX
RANGE SCAN                     EMP_SAL_BIT                           1

Here we see that the bitmap indexes are merged together with a BITMAP CONVERSION TO ROWIDS and the BITMAP AND execution method. This bitmap merge method will dramatically reduce the execution time of queries on large tables by merging the resulting ROWIDs from each bitmap index.

In summary, compound AND conditions can be serviced with an index by adding a concatenated index for B-tree values and bitmap indexes for columns values with low cardinality. When using bitmap indexes, you can invoke the index_combine hint to improve the performance of queries with compound AND predicates.

Next, let’s take a look at initialization parameters that affect the behavior of the cost-based optimizer.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

Compound AND Conditions in Boolean Predicates Oracle Tips by Burleson

Compound AND Conditions in Boolean Predicates
Oracle Tips by Burleson