Ensuring that predicates are coded correctly

Whether you code the predicates of your SQL statements correctly has a great effect on the performance of those queries.

To ensure the best performance from your predicates:

• Make sure all the predicates that you think should be indexable are coded so that they can be indexable.
  Refer to Predicate types and processing by stage to see which predicates are indexable and which are not.
• Try to remove any predicates that are unintentionally redundant or not needed; they can slow down performance.
• For string comparisons other than equal comparisons, ensure that the declared length of a host variable is less than or equal to the length attribute of the table column that it is compared to. For languages in which character strings are null-terminated, the string length can be less than or equal to the column length plus 1. If the declared length of the host variable is greater than the column length,in a non-equal comparison, the predicate is stage 1 but cannot be a matching predicate for an index scan.
  For example, assume that a host variable and an SQL column are defined as follows:

  C language declaration	SQL definition
  char string_hv[15]	STRING_COL CHAR(12)

  A predicate such as WHERE STRING_COL > :string_hv is not a matching predicate for an index scan because the length of string_hv is greater than the length of STRING_COL. One way to avoid an inefficient predicate using character host variables is to declare the host variable with a length that is less than or equal to the column length:

  char string_hv[12]

  Because this is a C language example, the host variable length could be 1 byte greater than the column length:

  char string_hv[13]

  For numeric comparisons, a comparison between a DECIMAL column and a float or real host variable is stage 2 if the precision of the DECIMAL column is greater than 15. For example, assume that a host variable and an SQL column are defined as follows:

  C language declaration	SQL definition
  float float_hv	DECIMAL_COL DECIMAL(16,2)

  A predicate such as WHERE DECIMAL_COL = :float_hv is not a matching predicate for an index scan because the length of DECIMAL_COL is greater than 15. However, if DECIMAL_COL is defined as DECIMAL(15,2), the predicate is stage 1 and indexable.