BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter