 |
|
Index Usage for Queries with IN Conditions
Oracle Tips by Burleson Consulting |
When a query has an IN condition to evaluate
multiple expressions, the SQL optimizer will often perform a
full-table scan, but with many variant execution plans. Queries that
evaluate multiple conditions can execute with TABLE ACCESS FULL,
CONCATENATION, INLIST ITERATOR, and UNION, which all perform the
exact same function of returning rows with multiple values.
To illustrate, consider the following simple
query.
select
ename
from
emp
where
job IN ('MANAGER','PRESIDENT')
;
Here you see the execution plan. Note the use
of the CONCATENATION operator.
OPERATION
--------------------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
CONCATENATION
1
TABLE ACCESS
BY INDEX ROWID
EMP
1
INDEX
RANGE SCAN
JOB_IDX
1
TABLE ACCESS
BY INDEX ROWID EMP
2
INDEX
RANGE SCAN
JOB_IDX
1
Now we change to the CBO by adding a
first_rows hint, and we see an entirely different execution
plan.
OPERATION
--------------------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
1
INLIST ITERATOR
1
TABLE ACCESS
BY INDEX ROWID
EMP
1
INDEX
RANGE SCAN
JOB_IDX
1
Of course, this query can also be rewritten
to utilize the union SQL operator. Here is an equivalent
query.
select /*+
first_rows */
ename
from
emp
where
job = 'MANAGER'
union
select ename from emp
where
job = 'PRESIDENT'
;
Here you see the execution plan using the
UNION-ALL table access method.
OPERATION
--------------------------------------------------------------------------------
OPTIONS
OBJECT_NAME POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
6
SORT
UNIQUE
1
UNION-ALL
1
TABLE ACCESS
BY INDEX ROWID
EMP
1
INDEX
RANGE SCAN
JOB_IDX
1
TABLE ACCESS
BY INDEX ROWID
EMP
2
INDEX
RANGE SCAN
JOB_IDX
1
Here you see three alternative execution
plans for the exact same result set. The point is that there are
many opportunities to change the execution plan for queries that
evaluate for multiple conditions, and in most cases you must
actually time the queries to see which execution plan is fastest for
your specific query.
Conclusion
This chapter has been concerned with the
appropriate use of indexes to service Oracle queries. The main
points of this chapter include these:
-
The number of table blocks
accessed by a query determines whether to use an index range scan
or a full-table scan. You must evaluate the number of blocks
returned by each form of the query to determine the fastest
execution path.
-
Using BIFs in a query or
performing computations on an index column can sometimes
invalidate the index and cause a full-table scan.
-
When using the RBO, bitmap and
function-based indexes will be ignored.
-
Oracle bitmap indexes have been
enhanced to allow for columns that possess up to 1000 distinct
values.
-
Oracle provides alternative
indexing methods including bitmap indexes, function-based indexes,
and reverse-key indexes to provide performance improvements for
specialized queries.
Next, let’s move on and look at how to tune
queries that maximize performance for Oracle data warehouses.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
