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 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';
