This example builds on the access plan described in Query 3 by
creating indexes on the JOB and SALARY columns in the STAFF table, and adding
DEPTNAME to the existing index in the ORG table. You can follow along interactively using the sample snapshots
provided.
1. What changed in this access plan as a result of creating additional indexes?
In the middle portion of the access plan graph, notice that for the ORG table, the previous index scan and fetch have been changed to an index scan only IXSCAN (7). Adding the DEPTNAME column to the index on the ORG table has allowed the optimizer to eliminate the extra access involving the fetch.
In the bottom portion of the access plan graph, note that for the STAFF table the previous index scan and fetch have been changed to an index scan only IXSCAN (20). Creating the JOB and SALARY index on the STAFF table has allowed the optimizer to eliminate the extra access involving the fetch.
2. How effective is this access plan?
This access plan is more cost effective than the one from the previous example. The cumulative cost has been reduced from approximately 224 timerons in Query 3 to approximately 132 timerons in Query 4.
The next section provides a summary of guidelines for tuning your SQL queries.
[ Top of Page | Table of Contents | Glossary | Index ]