Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

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

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.