Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter