This example builds on the access plan described in Query 1 by collecting
current statistics with the runstats command. You can follow along interactively using the sample snapshots
provided.
This example builds on the access plan described in Query 1 by collecting
current statistics with the runstats command. You can follow along interactively using the sample snapshots
provided.
1. Do current statistics exist for each table in the query?
The Table Statistics window for the ORG table shows that the optimizer used actual statistics (the STATS_TIME value is the actual time that the statistics were collected). The accuracy of the statistics depends on whether or not there were significant changes to the contents of the tables since the runstats command was run.
2. Does this access plan use the most effective methods of accessing data?
Like Query 1, the access plan in Query 2 uses table scans (TBSCAN) not index scans (IXSCAN). Even though current statistics exist, an index scan was not done because there are no indexes on the columns that were used by the query. One way to improve the query would be to provide the optimizer with indexes on columns that are used to join tables (that is, on columns that are used in join predicates). In this example, this is the first merge scan join: MSJOIN (4).
In the Operator Details window for the MSJOIN (4) operator, look at the Join predicates section under Input arguments. The columns used in this join operation are listed under the Text column. In this example, these columns are DEPTNUMB and DEPT.
3. How effective is this access plan?
Access plans based on up-to-date statistics always produce a realistic estimated cost (measured in timerons). Since the estimated cost in Query 1 was based on default statistics, we cannot compare the cost of the two access plan graphs to determine which one is more effective. Whether the cost is higher or lower is not relevant. You must compare the cost of access plans that are based on actual statistics to get a valid measure of effectiveness.
How to further refine this access plan
Add indexes on the columns that are used in join predicates.
The next example looks at the effects of adding indexes on the DEPTNUMB and DEPT columns.