 |
|
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
|
|
