 |
|
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.
|
