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

 

 


 

 

 

        
 
     Oracle:

 
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 B         
          ytes=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=1         
          0)                                                                   
                                                                               
   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 Byte         
          s=4)” 


 

     

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.