Avoiding expressions over columns in local predicates

Instead of applying an expression over columns in a local predicate, use the inverse of the expression.

Consider the following examples:

XPRESSN(C) = 'constant'
INTEGER(TRANS_DATE)/100 = 200802

You can rewrite these statements as follows:

C = INVERSEXPRESSN('constant')
TRANS_DATE BETWEEN 20080201 AND 20080229

Applying expressions over columns prevents the use of index start and stop keys, leads to inaccurate selectivity estimates, and requires extra processing at query execution time.

These expressions also prevent query rewrite optimizations such as recognizing when columns are equivalent, replacing columns with constants, and recognizing when at most one row will be returned. Further optimizations are possible after it can be proven that at most one row will be returned, so the lost optimization opportunities are further compounded. Consider the following query:

SELECT LASTNAME, CUST_ID, CUST_CODE FROM CUST 
WHERE (CUST_ID * 100) + INT(CUST_CODE) = 123456 ORDER BY 1,2,3

You can rewrite it as follows:

SELECT LASTNAME, CUST_ID, CUST_CODE FROM CUST 
WHERE CUST_ID = 1234 AND CUST_CODE = '56' ORDER BY 1,2,3

If there is a unique index defined on CUST_ID, the rewritten version of the query enables the query optimizer to recognize that at most one row will be returned. This avoids introducing an unnecessary SORT operation. It also enables the CUST_ID and CUST_CODE columns to be replaced by 1234 and '56', avoiding copying values from the data or index pages. Finally, it enables the predicate on CUST_ID to be applied as an index start or stop key.

It might not always be apparent when an expression is present in a predicate. This can often occur with queries that reference views when the view columns are defined by expressions. For example, consider the following view definition and query:

CREATE VIEW CUST_V AS
  (SELECT LASTNAME, (CUST_ID * 100) + INT(CUST_CODE) AS CUST_KEY
    FROM CUST)

SELECT LASTNAME FROM CUST_V WHERE CUST_KEY = 123456

The query optimizer merges the query with the view definition, resulting in the following query:

SELECT LASTNAME FROM CUST
WHERE (CUST_ID * 100) + INT(CUST_CODE) = 123456

This is the same problematic predicate described in a previous example. You can observe the result of view merging by using the explain facility to display the optimized SQL.

If the inverse function is difficult to express, consider using a generated column. For example, if you want to find a last name that fits the criteria expressed by

LASTNAME IN ('Woo', 'woo',
'WOO', 'WOo',...)

, you can create a generated column

UCASE(LASTNAME)
= 'WOO'

as follows:

CREATE TABLE CUSTOMER (
  LASTNAME VARCHAR(100),
  U_LASTNAME VARCHAR(100) GENERATED ALWAYS AS (UCASE(LASTNAME))
)

CREATE INDEX CUST_U_LASTNAME ON CUSTOMER(U_LASTNAME)

Support for case-insensitive search, which was introduced in Db2® Database for Linux®, UNIX, and Windows Version 9.5 Fix Pack 1, is designed to resolve the situation in this particular example. You can use _Sx attribute on a locale-sensitive UCA-based collation to control the strength of the collations. For example, a locale-sensitive UCA-based collation with the attributes _LFR_S1 is a French collation that ignores case and accent.