Access Path Strategy Compatibility

 <  Day Day Up  >  

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

- - -

- - -

- - -


 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net