The Essential Guide to 8 Index Access Paths in DB2

DB2 will use these index access paths. As said in IBM DB2 Manual: The access path for an SQL statement specifies how Db2 accesses the data that the query specifies. It specifies the indexes and tables that are accessed, the access methods that are used, and the order in which objects are accessed.

DB2 access path types

Here are the 8 top Index access paths.

Matching-index scan (MATCHCOLS > 0)

SELECT * FROM T
  WHERE C1=1 AND C2>1
    AND C3=1;

Index screening

SELECT * FROM T
  WHERE C1 = 1
    AND C3 > 0 AND C4 = 2
    AND C5 = 8;

Nonmatching-index scan (ACCESSTYPE = I and MATCHCOLS = 0)

--When index screening predicates exist
In that case, not all of the data pages are accessed.
--When the clause OPTIMIZE FOR n ROWS is used
That clause can sometimes favor a nonmatching index, especially if the index gives the ordering of the ORDER BY clause or GROUP BY clause.
--When more than one table exists in a non-segmented table space
In that case, a table space scan reads irrelevant rows. By accessing the rows through the nonmatching index, fewer rows are read.

IN-list index scan (ACCESSTYPE = N)

SELECT * FROM T
  WHERE C1=1 AND C2 IN (1,2,3)
    AND C3>0 AND C4<100;

Multiple-index access (ACCESSTYPE is M, MX, MI, or MU)

SELECT * FROM EMP
   WHERE (AGE = 34) OR
         (AGE = 40 AND JOB = 'MANAGER');

One-fetch access (ACCESSTYPE = I1)

SELECT MIN(C1) FROM T;
SELECT MIN(C1) FROM T WHERE C1>5;
SELECT MIN(C1) FROM T WHERE C1>5 AND C1<10;
SELECT MIN(C2) FROM T WHERE C1=5;
SELECT MAX(C1) FROM T;
SELECT MAX(C2) FROM T WHERE C1=5 AND C2<10;
SELECT MAX(C2) FROM T WHERE C1=5 AND C2>5 AND C2<10;
SELECT MAX(C2) FROM T WHERE C1=5 AND C2 BETWEEN 5 AND 10;

Index-only access (INDEXONLY = Y)

SELECT C1, C2 FROM T WHERE C1 > 0;
SELECT C1, C2 FROM T;
SELECT COUNT(*) FROM T WHERE C1 = 1;

Equal unique index (MATCHCOLS = number of index columns)

Unique Index1: (C1, C2)
Unique Index2: (C2, C1, C3)
 
SELECT C3 FROM T
  WHERE C1 = 1 AND C2 = 5;

Related