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