Access Path Strategy Compatibility The optimizer combines access path strategies to form an efficient access path. However, not all the strategies are compatible, as shown in Table 21.8. As you can plainly see, the optimizer must follow a mountain of rules as it performs its optimization. Here are some further notes on Table 21.8: -
Each access path is composed of at least one strategy and possibly many. A Yes in any block in the matrix indicates that the two strategies can be used together in a single access path; a No indicates incompatibility . -
For the join methods , the matrix entries apply to any one portion of the join (that is, the access path for either the inner table or the outer table). -
Sequential detection is always invoked in conjunction with sequential prefetch. -
Index-only access must be used in conjunction with one of the index access path strategies. -
For the hybrid join method, the inner table is always accessed with an index using a form of list prefetch; the outer table can be accessed using any access method deemed by the optimizer to be most efficient. You have covered a large number of topics under the heading of the DB2 optimizer. This should drive home the point that the optimizer is a complex piece of software. Although we know quite a bit about what the optimizer can do, we know little about how exactly it decides what to do. This is not surprising. IBM has invested a great amount of time, money, and effort in DB2 and has also staked a large portion of its future on DB2's success. IBM wouldn't want to publish the internals of the optimizer, thus enabling competitors to copy its functionality. The optimizer and the access paths it chooses are the most complex parts of DB2. Even though the subject is complex, an understanding of the optimizer is crucial for every user . This chapter fulfills this requirement. But where does the DB2 optimizer get the information to formulate efficient access paths? Where else ”from the DB2 Catalog, the subject of the next chapter. Table 21.8. Access Path Strategy Compatibility Matrix | Simple Tablespace Scan | Partitioned Tablespace Scan | Segmented Tablespace Scan | Sequential Prefetch/Detection | Query Parallelism | Direct Index Lookup | Matching Index Scan | Simple Tablespace Scan | - - - | No | No | Yes | Yes | No | No | Partitioned Tablespace Scan | No | - - - | Yes | Yes | Yes | No | No | Segmented Tablespace Scan | No | No | - - - | Yes | Yes | No | No | Sequential Prefetch/Detection | Yes | Yes | Yes | - - - | Yes | No | Yes | Query Parallelism | No | Yes | No | Yes | - - - | No | Yes | Direct Index Lookup | No | No | No | No | No | - - - | Yes | Matching Index Scan | No | No | No | Yes | Yes | Yes | - - - | Nonmatching Index Scan | No | No | No | Yes | Yes | No | No | Index Lookaside | No | No | No | No | No | No | Yes | Multi-Index Access | No | No | No | Yes | No | No | Yes | Index-Only Access | No | No | No | Yes | Yes | Yes | Yes | List Prefetch | No | No | No | No | No | No | Yes | Nested Loop Join | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Merge Scan Join | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Hybrid Join | Yes | Yes | Yes | Yes | Yes | Yes | Yes | | Non-Matching Index Scan | Index Lookaside | Multi-Index Access | Index-Only Access | List Prefetch | Nested Loop Join | Merge Scan | Hybrid Join | Simple Tablespace Scan | No | No | No | No | No | Yes | Yes | Yes | Partitioned Tablespace Scan | No | No | No | No | No | Yes | Yes | Yes | Segmented Tablespace Scan | No | No | No | No | No | Yes | Yes | Yes | Sequential Prefetch/Detection | Yes | No | No | Yes | No | Yes | Yes | Yes | Query Parallelism | Yes | No | No | Yes | No | Yes | Yes | Yes | Direct Index Lookup | No | No | No | Yes | No | Yes | Yes | Yes | Matching Index Scan | No | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Nonmatching Index Scan | - - - | Yes | No | Yes | No | Yes | Yes | Yes | Index Lookaside | Yes | - - - | No | Yes | No | Yes | Yes | Yes | Multi-Index Access | No | No | - - - | No | Yes | Yes | Yes | Yes | Index-Only Access | Yes | Yes | No | - - - | No | Yes | Yes | Yes | List Prefetch | No | No | Yes | No | - - - | Yes | Yes | Yes | Nested Loop Join | Yes | Yes | Yes | Yes | Yes | - - - | - - - | - - - | Merge Scan Join | Yes | Yes | Yes | Yes | Yes | - - - | - - - | - - - | Hybrid Join | Yes | Yes | Yes | Yes | Yes | - - - | - - - | - - - | |