Access Paths

An access path is the route DB2 takes to get your data: which index to use, in what order to join tables, and where sorts are needed. The optimizer picks it using statistics. Your job is to check it with EXPLAIN and fix it when it is bad.

What is an access path

• For every SQL statement, the DB2 optimizer chooses an access path: the cheapest way to fetch the rows.
• The choice depends on RUNSTATS statistics, available indexes, and the predicates in your WHERE clause.
• The same query can get a different access path after new statistics, a new index, or more data. That is why slow queries must be re-explained.
• You see the chosen path in PLAN_TABLE after running EXPLAIN, as learned on the previous page.

Table access methods

• Tablespace scan (ACCESSTYPE R): reads every page of the table. Fine for tiny tables or when you need most rows, terrible for one row out of millions.
• Index scan (ACCESSTYPE I): walks an index to find just the wanted rows. Best when the WHERE clause matches the index columns.
• Index-only access (INDEXONLY Y): every column the query needs is in the index, so DB2 never touches the table. The fastest access of all.
• Multiple index access (ACCESSTYPE M): DB2 combines two or more indexes with AND/OR to find the rows.

Join methods

• Nested loop join (METHOD 0): for each row of the outer table, look up matching rows in the inner table using its index. Best when the outer result is small.
• Merge scan join (METHOD 2): sorts both tables on the join columns, then merges them. Good when both sides are large and indexes are missing.
• Hybrid join (METHOD 4): scans the outer table once and joins using the inner table's index, a mix of the two above.
• Reading a sample PLAN_TABLE result:-
  QBNO ACCESSTYPE MATCHCOLS INDEXONLY METHOD TNAME 1 I 1 Y 0 EMP 1 I 2 N 0 DEPT -- Both tables use an index (I), nested loop join (0), -- EMP is index-only (Y): no table pages read.