Tuning Complex Boolean Queries
Oracle Tips by Burleson

It is not uncommon in a production environment to find very complex combinations of AND and OR logic in the where clause of your SQL statement. The order and combination of the Boolean values dictates to the SQL optimizer the execution plan that will be taken in order to service the query. For example, a SQL query with a lot of OR statements might invoke a CONCATENATION execution plan to determine the result set.

There are several types of complex Boolean constructs worth exploring, and this section will focus on queries with compound OR or compound AND conditions.

Compound OR Conditions in Boolean Predicates

There are cases in SQL statements where you may have a large chain of OR conditions that are ANDed together with other statements in the SQL query. This includes large in-lists (e.g., where owner in (‘SYS’,’SYSTEM’,’PERFSTAT’)). There are two flavors of compound OR statements in SQL syntax:

Where the same columns contain OR’ed values:

where
   status = ‘retired’
or
   status = ‘active
or
   status = ‘apprentice’
or
   state_of_residence IN (‘NY’,’NC’,’WA’,’HI’,’CO’);

Where each OR condition refers to a different column:

where
   (
      status = ‘retired’
    or
      state_of_residence = ‘NC’
    or
      department = ‘accounting’
   )
and
   (
      age > 65
    or
      department = ‘marketing
    or
      state_of_residence = ‘NY’
   );

For example, consider the following query for examples of OR expansion:

select /*+ first_rows */
   ename
from
   emp
where
   deptno in (10, 15, 20, 22, 26, 28, 31)
and
   (
       job = ‘CLERK’
     or
       job = ‘SALESMAN’
     or
       job = ‘SECRETARY’
   )
;

For this type of OR where all columns are the same, the cost-based execution plan depends on the presence of indexes. Here is the execution plan with bitmap indexes on job and deptno:

Next, let’s explore the more onerous query where the OR columns do not reference the same column. For example, consider the following query where a B-tree index exists on job, deptno, and sal. It is interesting to note that if the indexes were bitmap indexes, the execution would not perform a full-table scan. This is because Oracle automatically uses bitmap indexes where a query has multiple OR conditions on bitmap index columns.

select
   ename
from
   emp
where
   deptno = 10
or
   sal < 5000
or
   job = ‘CLERK’;

Here we have two choices. Because all of the index columns are low cardinality, we could create a bitmap index on deptno, sal, and clerk, causing a bitmap merge execution plan. Our other choice is to invoke the use_concat to break the query into three separate B-tree index scans whose result sets will be combined with the union operator.

The use_concat hint requests that a union all execution plan be used for all OR conditions in the query, rewriting the query into multiple queries. The use_concat hint is commonly invoked when a SQL query has a large number of OR conditions in the where clause.

Here is the execution plan for this query. Note that we must perform a full-table scan to satisfy the multiple OR conditions in the where clause:

If our indexes had been bitmap indexes, we would have seen a far faster execution plan using the BITMAP CONVERSION TO ROWIDS method of intersecting the bitmap indexes:

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

Now, let’s add the use_concat hint and see the change to the execution plan.

select /*+ use_concat */
   ename
from
   emp
where
   deptno = 10
or
   sal < 5000
or
   job = ‘CLERK’;

Here we see that the full-table scan has been replaced with a union of three queries, each using the B-tree index for the single columns and the CONCATENATION plan to UNION together the result sets.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     3
CONCATENATION
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
      INDEX
RANGE SCAN                     EMP_JOB                               1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
      INDEX
RANGE SCAN                     EMP_SAL                               1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   3
      INDEX
RANGE SCAN                     EMP_DEPT                              1

Now, let’s take a look at queries that contain compound AND predicates.

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

Tuning Complex Boolean Queries Oracle Tips by Burleson

Tuning Complex Boolean Queries
Oracle Tips by Burleson