 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|
|||
 |
|||
|
|
|||
 |
|||
|
|||
Tuning by Simplifying SQL Syntax
There are several methods for simplifying complex SQL statements, and Oracle10g will sometimes automatically rewrite SQL to make it more efficient.
§ Rewrite the query into a more efficient form
§ Use the WITH clause
§ Use Global Temporary Tables
§ Use Materialized Views
The following example shows how SQL can be rewritten. For a simple example of SQL syntax and execution speed, the following queries can be used. All of these SQL statements produce the same results, but they have widely varying execution plans and execution performance.
-- A non-correlated sub-query
select
book_title
from
book
where
book_key not in (select book_key from sales);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=64)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=64)
3 1 TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=25)
-- An outer join
select
book_title
from
book b,
sales s
where
b.book_key = s.book_key(+)
and
quantity is null;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=8200)
1 0 FILTER
2 1 FILTER
3 2 HASH JOIN (OUTER)
4 3 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=20 Bytes=1280)
5 3 TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=1800)
-- A Correlated sub-query
select
book_title
from
book
where
book_title not in (
select
distinct
book_title
from
book,
sales
where
book.book_key = sales.book_key
and
quantity > 0);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=59)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=59)
3 1 FILTER
4 3 NESTED LOOPS (Cost=6 Card=1 Bytes=82)
5 4 TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=90)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=1 Card=1)
7 6 INDEX (UNIQUE SCAN) OF 'PK_BOOK' (UNIQUE)
The formulation of the SQL query has a dramatic impact on the execution plan for the SQL, and the order of the WHERE clause predicates can make a difference. Savvy Oracle developers know the most efficient way to code Oracle SQL for optimal execution plans, and savvy Oracle shops train their developers to formulate efficient SQL.
The following section will show how the WITH clause can help simplify complex queries.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
 |
email BC:
Copyright © 1996 - 2010 by Burleson Enterprises, Inc. All
rights reserved. Oracle® is the registered trademark of Oracle Corporation.
|
|
