This example builds on the access plan described in Query 3 by
creating an index on the JOB column 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?
The optimizer has taken advantage of the index created on the JOB column in the STAFF table (represented by a diamond labeled I_JOB) to further refine this access plan.
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 (5). Adding the DEPTNAME column to the index on the ORG 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 2,420 timerons in Query 3 to approximately 2,281 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 ]