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

 

   
  Oracle Concepts by Burleson Consulting

SQL Query Re-Formulation

This exercise demonstrates how you can re-formulate an Oracle query to make the execution plan more efficient.

Your challenge is to write an SQL query that replicates the output from the following query, replacing the outer join with a non-correlated subquery.  The intent of this query is to display the names of all authors who have not yet written a book.

-- Find authors without any books

select
   a.author_key,
   author_last_name
from
   author       a,
   book_author ba
where
   a.author_key = ba.author_key(+)
and
   ba.author_key is null
;

Re-write the above query as a non-correlated subquery and use the autotrace utility to show any differences in the execution plans for the queries. Submit the listing to your instructor and describe which form of this query is the most readable and will execute fastest.

ANSWER

SQL> -- Find authors without any books

SQL> select
  2  a.author_key,
  3  author_last_name
  4  from
  5  author           a,
  6  book_author ba
  7  where
  8  a.author_key = ba.author_key(+)
  9  and
 10  ba.author_key is null
 11  ;

AUTHOR_KEY  AUTHOR_LAST_NAME                                                   
----------- ----------------------------------------                           
A108        mee                                                                
A107        clark      
                                                       

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=25 Bytes=350)         
   1    0 FILTER                                                             
   2    1 HASH JOIN (OUTER)                                                
   3    2 TABLE ACCESS (FULL) OF 'AUTHOR' (Cost=1 Card=10 Bytes=100)         
 
   4    2 TABLE ACCESS (FULL) OF 'BOOK_AUTHOR' (Cost=1 Card=25 Bytes=100)      

SQL>
SQL> -- Find authors without any books
SQL> select
  2  author_key,
  3  author_last_name
  4  from
  5  author
  6  where
  7  author_key not in (select author_key from book_author);


AUTHOR_KEY  AUTHOR_LAST_NAME
----------- ----------------------------------------                            
A107        clark                                                              
A108        mee                                                                

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=1 Bytes=10)         
   1    0 FILTER
   2    1 TABLE ACCESS (FULL) OF 'AUTHOR' (Cost=1 Card=1 Bytes=10)
   3    1 TABLE ACCESS (FULL) OF 'BOOK_AUTHOR' (Cost=1 Card=1 Bytes=4)"pop up window,              AJS]

The following questions are purely for you to measure your level of understanding.  After reading each of the following questions, answer the question in your own words.  Post your answers on the discussion forum, and read other students’ answers.

1. What is the difference between a declarative data access language and a navigational data access language?

Answer: A navigational data language requires knowledge of the internal tables and index structures.  SQL is a better database access language because the SQL optimizer takes care of the internal navigation.

2. What decisions does the SQL optimizer need to make when deciding upon the optimal execution plan?

Answer:

A) The order that the tables will be joined together

B) The choice of indexes

C) The internal joins methods (sort merge, hash, nested loops).

D) The method to sort the result set (use an index or invoke a sort)


3. Why is data independence an important feature of relational databases and SQL?

Answer:

Data independence means that related tables can be joined at any time without having to pre-establish the relationship.  Hence, any two tables will a common column can be joined together at runtime.

4. Why is the choice of the SQL optimizer goal an important consideration when tuning an SQL statement?

Answer:

Different optimizer modes have different goals and different internal weights.  The all_rows goal favors full-table scans and minimization of machine resources while the first_rows optimizer goal favors index-scans and return data back to the requestor as soon as possible.

5. Why is database design important to SQL performance?  How can databases be designed to make SQL statements run faster?

Answer:

By introducing redundancy into the data model (denormalization) expensive table joins can be avoided.

6. Why is searching for large-table full-table scans critical to SQL tuning?

Answer:

If the optimizer gets confused or cannot find an appropriate index that matches the WHERE clause, the optimizer will read every row in the table.  Hence large-table full-table scans often indicate a missing index or a sub-optimal choice of optimizer goal.

7. What is the relationship between indexes and SQL performance?

Answer:

The sole purpose of indexes is to make SQL queries run faster.  If the optimizer detects an indexes that matches part of the WHERE clause of the query, then the optimizer will use the index to avoid having to read every row in the table.



 
For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

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.