 |
|
Hints in Subqueries
Oracle Tips by
Burleson
|
Many Oracle professionals are not aware that
every subquery in a SQL statement can use hints to improve the
execution plan of the subquery.
We need to note that a table-specific hint
placed in the outer query will not be pushed through into the
subquery. Remember, an index hint must have the table and index
name and if the table
name is an alias (“a” in this case) you must reference the alias in
the hint:
select /*+
INDEX(a emp_idx) */
a.empno
from
dept b,
emp a,
salgrade c
where
EXISTS (
select
a.empno
from
dept b,
emp a,
salgrade c
where
a.deptno=b.deptno
and
a.job <> 'clerk'
and
a.sal between c.losal and
c.hisal
)
and
a.deptno=b.deptno
and
a.job <> 'clerk'
and
a.sal between losal and hisal;
Here is one of the least efficient execution
plans possible, the dreaded Cartesian merge scan. For each row in the
outer query, the inner query will be re-executed. This is an extremely
inefficient way of perform this query, and we know that it would run
far faster if we were able to tell it to utilize an index for the
subquery.
WARNING: Although the Oracle SQL
optimizer will automatically try to rewrite SQL and will actually use
a Cartesian product to accomplish some queries, seeing a Cartesian
join in your execution plan is always a cause for concern.
Whenever
you see a CARTESIAN table access method, always check to ensure that
all of your tables have the proper join clauses in your where
clause. For example, if you are joining eight tables, you should have
seven equality conditions in the where clause to specify the
join keys for the tables.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
538344
FILTER
NESTED LOOPS
MERGE JOIN
CARTESIAN
1
INDEX
FULL SCAN
DEPT_DEPT
1
SORT
JOIN
2
TABLE ACCESS
FULL
SALGRADE
1
TABLE ACCESS
BY INDEX ROWID
EMP
2
INDEX
RANGE SCAN
DEPT_EMP
1
NESTED LOOPS
MERGE JOIN
CARTESIAN
1
INDEX
FULL SCAN
DEPT_DEPT
1
SORT
JOIN
2
TABLE ACCESS
FULL
SALGRADE
1
TABLE ACCESS
BY INDEX ROWID
EMP
2
INDEX
RANGE SCAN
DEPT_EMP
1
Now, we move the hint from the outer query to the
subquery. Note that the index hint is now specified inside the
exists clause.
select
a.empno
from
dept b,
emp a,
salgrade c
where
EXISTS (
select /*+ INDEX(a amp_idx) */
a.empno
from
dept b,
emp a,
salgrade c
where
a.deptno=b.deptno
and
a.job <> 'clerk'
and
a.sal between c.losal and c.hisal
)
and
a.deptno=b.deptno
and
a.job <> 'clerk'
and
a.sal between losal and hisal;
Here we see that the execution plan has changed
and the Cartesian merge join has disappeared:
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
108281
FILTER
1
NESTED LOOPS
1
NESTED LOOPS
TABLE ACCESS
FULL
SALGRADE
1
TABLE ACCESS
BY INDEX ROWID
EMP
2
INDEX
FULL SCAN
JOB_IDX
1
INDEX
RANGE SCAN
DEPT_DEPT
2
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS
FULL
SALGRADE
1
TABLE ACCESS
BY INDEX ROWID
EMP
2
INDEX
FULL SCAN
JOB_IDX
1
INDEX
RANGE SCAN
DEPT_DEPT
2
In summary, hints in a subquery are recognized and do
affect the execution plan for that subquery. They do not, however,
affect the execution plan of the outer query.
Now let’s wrap up this chapter with a review of
the major points about SQL query hints.
Conclusion
Oracle hints are the most common tools for tuning
Oracle SQL. For hints to be effective, you must thoroughly understand
the compatibility between hints and table access methods. The main
points of this chapter include these:
-
Because hints are placed inside comments, they will be ignored if
the hint is incompatible with the existing execution plan or when
the hint is formatted improperly.
-
When using the RBO, hints can be used to change specific queries to
use the CBO. Always remember to analyze all table and indexes that
participate in the query.
-
When using the CBO, you can start tuning a suspect SQL statement by
adding the rule or first_rows hint.
-
Hints can be applied to subqueries, but a hint in the outer query
will not carry over into the subquery.
Next let’s take a look at SQL tuning with the
Oracle optimizer plan stability feature.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
