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

 

 

   
 

Column Uniqueness and Subquery Transformation
Oracle Tips by Burleson
 

Whenever an Oracle SQL tuning professional sees a subquery in a SQL statement, his or her first inclination is to see if the query can be rewritten as a standard join. However, this can be very dangerous unless you know whether the subquery is querying on unique values.

When the subquery has multiple tables in the from clause, explicit uniqueness can only occur when either of the following are true:

  • The columns defining a unique key in the lowest table of the hierarchy are on the select list of the subquery.

  • At least one of the columns defining the unique key is on the select list, and the other columns defining the unique key have an equality criterion specified, directly or indirectly.

If a subquery is rewritten to specify the subquery table in the from clause, the result set had better return only a single row, or otherwise the transformed query will return the wrong answer. Let’s illustrate this fact with a simple example. Assume that we want a quick list of all employees who have spent at least one year in the Accounting department. Since the department name is in the dept table, the following query could be used:

select /*+ first_rows */
   ename,
   hiredate
from
   emp
where
   hiredate < sysdate-365
and
   deptno IN
      (
      select deptno from dept
      where dname = ‘ACCOUNTING’
      );

Assuming that we have indexes on dname in the dept table, Oracle will perform a nested loop scan.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     7

  NESTED LOOPS
                                                                     1
    VIEW
                               VW_NSO_1                              1
      SORT
UNIQUE                                                               1
        TABLE ACCESS
FULL                           DEPT                                  1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
      INDEX
RANGE SCAN                     DEPT_EMP                              1

Now, it might be tempting to rewrite this query to replace the subquery with a join:

select /*+ first_rows */
   ename,
   hiredate
from
   emp,
   dept
where
   emp.deptno = dept.deptno
and
   hiredate < sysdate-365
and
   dname = 'ACCOUNTING'
;

Can you see the problem with this query? Remember, the department name is not unique. Here is the execution plan:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     5

  NESTED LOOPS
                                                                     1

    TABLE ACCESS
FULL                           DEPT                                  1

    TABLE ACCESS
BY INDEX ROWID                 EMP                                   2

      INDEX
RANGE SCAN                     DEPT_EMP                              1

Here we see that the execution plan has changed, but we have a real problem here, because this query might not always return the same result as the subquery. The reason is that the dname column is not unique.

Why is the unique key required to transform a subquery into a join? The simple reason is that without the uniqueness guarantee, it is possible for the transformed query to produce a different result set. This is because when uniqueness is not guaranteed, multiple rows may be joined to the row in the surrounding query, thus producing a Cartesian product effect.

Next, let’s examine the rare case where a correlated subquery uses the IN clause.

Correlated Subqueries with the IN Clause

This type of query form is redundant because the IN clause is doing exactly the same thing as the correlation in the subquery. To illustrate, consider the following query:

select /*+ first_rows */
   ename,
   hiredate
from
   emp
where
   deptno IN
      (
      select deptno from dept
      where emp.deptno = dept.deptno
      );

Here we see that this query contains redundant Boolean operators. The where deptno IN clause performs the same check as the where emp.deptno = dept.deptno clause.

Non-correlated Subqueries with the EXISTS Clause

As I already noted, it is never appropriate to specify a non-correlated subquery with the EXISTS clause. This is because a Cartesian product results from the execution.

For example, the following query might be used to attempt to count those employees who have bad credit.

select
   ename
from
   emp e
where EXISTS
   (select
      null
   from
      bad_credit b
   )
;

However, since each employee may have many bad_credit rows, and we do not specify a join condition, Oracle will perform a Cartesian join.

Next, let’s look at performing correlated subqueries with the EXISTS clause.


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