How to Find Suspicious Access Paths

http://www.1kuaiqian.com/1/post/2010/03/suspicious-access-paths.html

 

Keywords: Join, Index Scan, Relational Scan, Sort, RID pool

1 Sort Merge Join Analysis
Check whether the number of join columns selected by optimizer equals to the number of join predicates. If the number of join columns is less than the number of join predicates, then it could be a disaster if the two joined tables are very large. If such join predicates can be evaluated during join as usual, the possible disaster could be avoided.


2 Nested Loop Join Analysis
Check if the access method for the inner table is table scan. It is generally a bad access path if a large number of records returned after the outer table is accessed. 

3 Relational Scan Analysis
Check whether DB2 optimizer chooses to access the table by using a relational scan. Relational scan is generally not the best access method for accessing a table.

4 Nonmatching Index Scan Analysis
Check whether a nonmatching index scan is used to access a table. All the index keys must be examined. And because a nonmatching index usually provides no filtering, only a few cases provide an efficient access path.

5 RID Pool Usage Analysis
Check whether RID pool is used or not. List prefetch has the possibility to be fall backed to a table space scan.

6 Individual Sort Analysis
Check whether sort is used for join processing, group by, order by, duplicates removing and subquery processing. It could be possible to write the query differently to allow for sort avoidance or design an index to avoid a sort. 

7 Cartesian Join Analysis
Check whether DB2 optimizer chooses a join sequence that contains a Cartesian join. A Cartesian join is generally not the best access path if the number of qualified rows from the outer table or the inner table is large.