Coding for effective indexes

 

The following topics provide suggestions that will help you to design code which allows DB2 Universal Database™ for iSeries™ to take advantage of available indexes:

• Avoid numeric conversions
  When a column value and a host variable (or constant value) are being compared, try to specify the same data types and attributes. DB2 Universal Database for iSeries does not use an index for the named column if the host variable or constant value has a greater precision than the precision of the column. If the two items being compared have different data types, DB2 Universal Database for iSeries will need to convert one or the other of the values, which can result in inaccuracies (because of limited machine precision).

• Avoid arithmetic expressions
  Do not use an arithmetic expression as an operand to be compared to a column in a row selection predicate. The optimizer does not use an index on a column that is being compared to an arithmetic expression. While this may not cause an index over the column to become unusable, it will prevent any estimates and possibly the use of index scan-key positioning on the index. The primary thing that is lost is the ability to use and extract any statistics that might be useful in the optimization of the query.

• Avoid character string padding
  Try to use the same data length when comparing a fixed-length character string column value to a host variable or constant value. DB2 Universal Database for iSeries does not use an index if the constant value or host variable is longer than the column length.

• Avoid the use of like patterns beginning with % or _
  The percent sign (%), and the underline (_), when used in the pattern of a LIKE (OPNQRYF %WLDCRD) predicate, specify a character string that is similar to the column value of rows you want to select. They can take advantage of indexes when used to denote characters in the middle or at the end of a character string.

 

Parent topic:

Creating an index strategy