 |
|
Rewriting SQL Statements to Change the Table
Access Method
Oracle Tips by Burleson Consulting |
There are many ways that a SQL statement can
be changed to change the table access method. In the RBO, we can
switch the table order in the from clause to change the
driving table, and the order of the expressions in the where
clause can change the table access method. Because of the many ways
that a query can be rewritten, one of the common SQL tuning
techniques is rewriting the SQL source. However, there are many
hidden traps in query rewriting, and you must be very careful to
ensure that your rewrite is equivalent to the original expression.
This is especially true when rewriting
correlated and non-correlated subqueries into standard joins to
improve table access method. 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 that the
subquery is querying on unique values.
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. Returning to our original example,
let's try to count the number of employees who have ever received a
bonus. This exercise assumes that the ename column is not
unique.
select /*+
first_rows */
count(*)
from
emp
where
ename IN (select ename from bonus)
;
Here is the output of the query and the execution
plan:
COUNT(*)
----------
2
OPERATION
----------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
5
SORT
AGGREGATE
1
MERGE JOIN
1
SORT
JOIN
1
VIEW
VW_NSO_1
1
SORT
UNIQUE
1
TABLE ACCESS
FULL BONUS
1
SORT
JOIN
2
TABLE ACCESS
FULL EMP
1
Here we see a merge join, which translates into a
full-table scan of both tables. As you know, a merge join does not
rely on indexes and can be very time consuming because of the time
required to perform the full-table scans.
It might be tempting to rewrite this query to
replace the subquery with a standard join by moving the subquery
table into the from clause and adding a condition to the
where clause. This is a very common SQL tuning technique, and
when done properly, it can result in huge performance gains.
select /*+
first_rows */
count(*)
from
emp,
bonus
where
emp.ename = bonus.ename
;
Can you see the problem with this query?
Remember, the employee name is not unique, and we cannot guarantee
that there will be only one bonus for each employee. If there are
employees who have received multiple bonuses, the count will be
incorrect. In this case, rewriting the query to replace the
non-correlated subquery with a standard join has not resulted in an
equivalent query.
Here is the output and the execution plan.
Note that it returns the wrong count, and instead of a count of the
number of employees who have ever received a bonus, we see a
Cartesian product of both tables!
COUNT(*)
----------
6
OPERATION
----------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
SORT
AGGREGATE
1
NESTED LOOPS
1
TABLE ACCESS
FULL BONUS
1
INDEX
RANGE SCAN ENAME_IDX
2
Here we see that the execution plan has changed
favorably, and the full-table against the emp table has disappeared,
but we have a real problem here because this query is not equivalent
to the original subquery.
Why is a 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.
Warning: When rewriting subqueries to
improve performance, always verify that there are unique indexes
into both of the tables being joined. Otherwise, the reformulated
query might return the incorrect result.
Again, we will go into great detail about
this issue in Chapter 19, but for now you need to clearly understand
that while rewriting subqueries can change the table access method
and result in huge performance gains, it can be dangerous to attempt
to rewrite subqueries unless you know that the reformulated query
will return the identical result set.
Next, let’s look at how table rows can be
re-sequenced to improve the throughput of index range scans.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
