This example builds on the access plan described in Query 2 by creating
indexes on the DEPT column on the STAFF table and on the DEPTNUMB column on
the ORG table. You can follow along
interactively using the sample snapshots provided.
This example builds on the access plan described in Query 2 by creating
indexes on the DEPT column on the STAFF table and on the DEPTNUMB column on
the ORG table. You can follow along
interactively using the sample snapshots provided.
1. What has changed in the access plan with indexes?
Two new diamond-shaped nodes, I_DEPTNUMB and I_DEPT, have been added just above the ORG and STAFF tables, respectively. These nodes represent the indexes that were created on DEPTNUMB and DEPT, and show that the optimizer used index scans instead of a table scans to determine which rows to retrieve.
2. Does this access plan use the most effective methods of accessing data?
The access plan for this query shows the effect of creating indexes on the DEPTNUMB column of the ORG table, resulting in FETCH (9) and IXSCAN (12); and on the DEPT column of the STAFF table, resulting in FETCH (25) and IXSCAN (28). Query 2 did not have these indexes; therefore, table scans were used in that example.
The Operator Details window for the FETCH(9) operator shows the columns being used in this operation. You can see that DEPTNAME and DEPTNUMB are listed in the first two rows beside Columns retrieved under Input arguments.
The Operator Details window for the FETCH (25) operator shows the columns being used in this operation. You can see that SALARY and JOB are listed in the first two rows beside Columns Retrieved under Input Arguments.
The combination of index and fetch were calculated to be less costly than the full table scan used in the previous access plans.
3. 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 253 timerons in Query 2 to approximately 224 timerons in Query 3.
How to further refine this access plan
Create additional indexes to attempt to reduce the estimated cost for the access plan.
The next example reduces the fetch and index scan to a single index scan without a fetch. We'll add DEPTNAME to the index we created previously on the ORG table. (Adding a separate index could cause an additional access.) On the STAFF table, we'll create a combined index on the JOB and SALARY columns.