Instances where an index is not used

 

DB2 Universal Database™ for iSeries™ does not use indexes in the following instances:

• For a column that is expected to be updated; for example, when using SQL, your program might include the following:

  EXEC SQL  DECLARE DEPTEMP CURSOR FOR     SELECT EMPNO, LASTNAME, WORKDEPT       FROM CORPDATA.EMPLOYEE       WHERE (WORKDEPT = 'D11' OR
                 WORKDEPT = 'D21') AND
                EMPNO = '000190'
        FOR UPDATE OF EMPNO, WORKDEPT END-EXEC.

  When using the OPNQRYF command, for example:

  OPNQRYF FILE((CORPDATA/EMPLOYEE)) OPTION(*ALL)
     QRYSLT('(WORKDEPT *EQ ''D11'' *OR WORKDEPT *EQ ''D21'')
     *AND EMPNO *EQ ''000190''')

  Even if you do not intend to update the employee's department, the system cannot use an index with a key of WORKDEPT.

  The system can use an index if all of the updateable columns used within the index are also used within the query as an isolatable selection predicate with an equal operator. In the previous example, the system uses an index with a key of EMPNO.

  The system can operate more efficiently if the FOR UPDATE OF column list only names the column you intend to update: WORKDEPT. Therefore, do not specify a column in the FOR UPDATE OF column list unless you intend to update the column.

  If you have an updateable cursor because of dynamic SQL or the FOR UPDATE clause was not specified and the program contains an UPDATE statement then all columns can be updated.

• For a column being compared with another column from the same row. For example, when using SQL, your program might include the following:

  EXEC SQL  DECLARE DEPTDATA CURSOR FOR     SELECT WORKDEPT, DEPTNAME       FROM CORPDATA.EMPLOYEE       WHERE WORKDEPT = ADMRDEPT END-EXEC.

  When using the OPNQRYF command, for example:

  OPNQRYF FILE (EMPLOYEE) FORMAT(FORMAT1)
     QRYSLT('WORKDEPT *EQ ADMRDEPT')

  Even though there is an index for WORKDEPT and another index for ADMRDEPT, DB2 Universal Database for iSeries will not use either index. The index has no added benefit because every row of the table needs to be looked at.

 

Parent topic:

Indexes and the optimizer