Typical Access Paths

 

Typical Access Paths

Db2 determine an access path for every executable SQL statement. To do this, it looks at several things, such as the catalog statistics and configuration parameters. It takes into consideration how large a table is, whether there is an index on the table, and so forth.

You are capable of predicting and influencing the access path by the way you write your SQL statement.

1.       Relation scan

扫描整个表（where语句中的column没建索引）

2.       Matching index scan

从根索引开始找，到叶子索引，最后到相应的data pages中取数据（where语句中的column已建索引，但select语句中存在没建索引的column）

3.       Index-only access

从索引中就能找到所有数据,不在access the data pages（where语句中的column已建索引，并且select语句中的column都完全覆盖在此索引中）

4.       Non-matching index scan

从叶子索引开始扫描，然后在相应的data pages中取数据（where语句中的column已建索引，但不是leading part of the index key, 并且select语句中存在没建索引的column）