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 Tips by Burleson

OCP Instructors Guide for Oracle DBA Certification

Chapter 7 - Tuning and Performance

Using Explain Plan to Determine Access Paths

PUBLISHER_REP_FIRM. Oracle must read every row in the outer-table to determine if a matching row can be found in the inner-table (APPROVAL_STATUS). You will never get the optimizer to use an index on the outer-table’s join column. Oracle must read every row of the outer-table to determine if it has matching rows on the inner-table. The optimizer may choose an index on a non-join column in the outer-join table (if one is available). It’s really common sense. Oracle doesn’t know if there is match on the inner-table until it actually does the match! It must read every row in the outer-table to determine if there are matching values in the inner-table.

  • Operation 5.1 – Oracle is able to use an index on the join column of the inner-table (APPROVAL_STATUS). That’s because it knows the value being passed from the outer-table (PUBLISHER_REP_FIRM) during the execution of the join. It knows the value being passed to perform the match so it is able to use the unique index (PK_APPROVAL_STATUS) to get the ROWIDs of rows that match.
     

  • Operation 4.2 – Oracle is using the ROWID retrieved in Step 5.1 to retrieve additional column values from the inner-table (APPROVAL_STATUS).
     


The above text is an excerpt from:


OCP Instructors Guide for Oracle DBA Certification
A Study Guide to Advanced Oracle Certified Professional Database
Administration Techniques

ISBN 0-9744355-3-8

by Christopher T. Foot
 

http://www.rampant-books.com/book_2003_2_OCP_print.htm


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.