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

 

 

   
 

Tuning Subqueries with Non-Equality Conditions
Oracle Tips by Burleson
 

In addition to subqueries where the inner query is tied to the outer query with an equality operator, you can also specify other conditions before the subquery. This section will examine how Oracle processes subqueries using the ANY, ALL, and standard non-equality conditions such as greater than (>), less than (<) and not equals (<>).

Let’s begin by reviewing the ALL and ANY clauses.

Using the ANY or ALL Conditions in a Subquery

The ANY and ALL operators are used with subqueries when you want to return matching values from the subquery to the outer query. This is a relatively obscure technique, but it is supported in Oracle SQL, and it is used from time to time when the need arises to compare scalar values in two tables. We will see that the ALL or ANY clauses can easily be replaced by in-line views that simplify both the structure of the query and the internal performance of the query. Let’s start with the ANY clause.

Using the ANY Clause in a Subquery

For example, consider the “> ANY” operator as shown next. Here our user is trying to display the names of all employees who are younger than any customer born after 1985. Why would a user want to know this information, you ask? The technical term for this types of query is “brain fart,” and obtuse queries like this one are generated every day by SQL developers.

select
   ename
from
   emp
where
   birthdate > ANY
   (select
      birthdate
   from
      customer
   where
      birthdate > '31-DEC-1985'
   )
;

This query will return all employee names where their birthdate > any customers born after 1985. Here is the execution plan.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     1
  FILTER
                                                                     1
    TABLE ACCESS
FULL                           EMP                                   1
    TABLE ACCESS
FULL                           CUSTOMER                              2

The “> ANY” operator says to return values where any returned value in the subquery is true. Note that this is equivalent to returning saying that if the employees’ birthdate is greater than the minimum customer birthdate that is returned from the subquery.

Hence, we can replace the “> ANY” with a non-equality and select the minimum values in an in-line view. As you may recall, an in-line view is a query where a subquery is specified in the from clause, just as if it were a table name.

select
   ename
from
   emp,
   (select
      min(birthdate) min_bday
    from
      customer
    where
      birthdate > '31-DEC-1985'
   ) in_line_view
where
   emp.birthdate > in_line_view.min_bday
;

The ALL operator is a bit different from the ANY operator. To illustrate, look at the following query:

select
   ename
from
   emp
where
   birthdate > ALL
   (select
      birthdate
   from
      customer
   where
      birthdate > '31-DEC-1985'
   )
;

This query says to display the names of all employees whose birthdate is greater than any customers who were born after 1985.

It should now be apparent that the ANY operator returns the minimum value from the in-line view, and the ALL operator returns the maximum value from the in-line view.  Here is an equivalent query:

select
   ename
from
   emp,
   (select
      max(birthdate) max_bday
    from
      customer
    where
      birthdate > '31-DEC-1985'
   ) in_line_view
where
   emp.birthdate > in_line_view.min_bday
;

In sum, the ALL and ANY subquery forms can always be replaced with in-line views, and the in-line view form of the query has far greater performance because it can utilize the indexes on the joined tables.

Next, let’s look at other forms of subqueries that have non-equality operators.


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