use_concat Hint

The use_concat Hint

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 amount of OR conditions in the where clause.

In other words, use of the use_contact hint forces combined OR conditions and IN processing in the where clause to be transformed into a compound query using the union all set operator.

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 three bitmap indexes on deptno, sal and clerk, causing a bitmap merge execution plan. Our other choice is to invoke use_concat to break the query into three separate B-tree index scans whose result sets will be combined with the union operator.

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

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------  
SELECT STATEMENT                                                     1

   TABLE ACCESS
FULL                            EMP                                  1

If our indexes had been bitmap indexes, we would have seen a far faster execution plan:

 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, returning to our example with three B-tree indexes, 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 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

There are times when it is possible for Oracle to ignore your use_concat hint.  While it is true Oracle will generally not ignore an properly referenced use_concat or other hint, the following conditions can result in failure of your use_concat or other hint to achieve the desired result:

• Syntax errors: Misspelling the hint will cause it to be ignored.  For example, use_concat will be used but use_contac will not and will be ignored:
  
  select /*+ use_contac */
      ename
  from
      emp
  where
      deptno = 10
  or
      sal < 5000
  or
      job = ‘CLERK’;
• Semantics errors: If a use_concat hint argument is misspelled, it will be ignored.
  
  select /*+ use_concat */
      enane
  from
      emp
  where
      deptno = 10
  or
      sal < 5000
  or
      job = ‘CLERK’;