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

 

 

   
 

Using Non-Equality Conditions in Subqueries
Oracle Tips by Burleson
 

For example, consider a subquery where the inner query returns many rows.

Here we add the following table to our schema, used to track each employee’s salary history:

SQL> select * from sal_hist;

     EMPNO     SALARY EFFECTIVE
---------- ---------- ---------
      7369       4000 31-MAR-01
      7499       4000 31-MAR-01
      7369       5000 20-APR-01
      7499       5000 24-APR-01

Because each employee may have many rows in the sal_hist table, we cannot create a unique index on the empno column.

The following query returns the last effective date for a raise, selecting only those employees who have not received a raise in the past 90 days:

select
   ename
from
   emp       e,
   sal_hist  s1
where
   e.empno = s1.empno
and
   effective_date <
   (select
      max(effective_date)
   from
      sal_hist s2
   where
      s1.empno=s2.empno
   and
      s2.effective_date <= sysdate-90
   )
;

Here is the execution plan. Note that we see the VIEW table access method that is always performed in a subquery.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
OTHER_TAG
----------------------------------------------------------------------
SELECT STATEMENT
                                                                  1385
  NESTED LOOPS
                                                                     1
    VIEW
                               VW_NSO_1                              1
      MINUS
                                                                     1
        SORT
UNIQUE                                                               1
          TABLE ACCESS
FULL                           EMP                                   1
        SORT
UNIQUE                                                               2
          TABLE ACCESS
FULL                           BAD_CREDIT                            1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
      INDEX
RANGE SCAN                     EMP_EMPNO                             1

Here we see an execution plan using the VIEW access method, and we are doing a complex and time-consuming subquery. Since this correlated subquery returns an aggregate (max, min, sum, or avg), we can use a temporary table to store the maximum values for each employee’s salary effective_date. As you will remember from Chapter 18, temporary tables are great for speeding up queries by moving the pre-computed aggregation to another table.

TIP: Never underestimate the power of temporary tables. Whenever your SQL is performing multiple aggregations, using temporary tables can often improve the speed of the query by several orders of magnitude. For details on using temporary tables to tune SQL, please see Chapter 18.

drop table
   temp;

create table
   temp
as
select
   empno,
   max(effective_date) max_date
from
   sal_hist
group by
   empno
;

select
   ename
from
   emp e,
   temp t
where
   e.empno=t.empno
and

   t.max_date < sysdate-90
;

Here we have greatly improved the speed of the overall query, and we have also simplified the query syntax and the execution plan.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                    19
  NESTED LOOPS
                                                                     1
    TABLE ACCESS
FULL                           TEMP                                  1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
      INDEX
RANGE SCAN                     EMP_EMPNO                             1

Next, let’s take a look at how hints are used to improve execution speed for subqueries.


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