Predicates generated through transitive closure

When the set of predicates that belong to a query logically imply other predicates, Db2 can generate additional predicates to provide more information for access path selection.

A basic example of transitive closure is a query that contains both of the following predicates: COL1=COL2 and COL2=COL3. Given these two predicates, logic implies that a third predicate COL1=COL3 is also valid, even though that predicate might not exist in the statement. In the event that this third predicate might enable Db2 to choose a more efficient access path, Db2 might generate the additional predicate.

Rules for generating predicates

Begin program-specific programming interface information.For single-table or inner join queries, Db2 might generate predicates for transitive if the following conditions are true:

  • The query has an equal type local or join predicate, such as: COL1=COL2. The query also has a Boolean term predicate on one of the columns in the first predicate, with one of the following formats:
  • The query also contains a Boolean term predicate on one of the columns in the first predicate, with one of the following formats:
    • COL1 op value

      op is =, <>, >, >=, <, or <=.

      value is a constant, host variable, or special register.

    • COL1 (NOT) BETWEEN value1 AND value2
    • COL1=COL3
  • The query contains an IN-list predicate, such as COL1 IN (value1, value2, value3)
  • The query is an outer join query and has an ON clause in the form of COL1=COL2 that comes before a join that has one of the following forms:
    • COL1 op value

      op is =, , >, >=, <, or <=

    • COL1 (NOT) BETWEEN value1 AND value2

Db2 generates a transitive closure predicate for an outer join query only if the generated predicate does not reference the table with unmatched rows. In other words, the generated predicate cannot reference the left table for a left outer join or the right table for a right outer join.

For a multiple-CCSID query, Db2 does not generate a transitive closure predicate if the predicate that would be generated has any of the following characteristics:

  • The generated predicate is a range predicate (op is >, >=, <, or <=).
  • Evaluation of the query with the generated predicate results in different CCSID conversion from evaluation of the query without the predicate.

When a predicate meets the transitive closure conditions, Db2 generates a new predicate, whether or not it already exists in the WHERE clause.

The generated predicates have one of the following formats:

  • COL op value

    op is =, >, >=, <, or <=.

    value is a constant, host variable, or special register.

  • COL (NOT) BETWEEN value1 AND value2
  • COL1=COL2 (for single-table or inner join queries only)

Db2 does not generate a predicate through transitive closure for any predicate that is associated with the DECFLOAT data type (column or constant).

Example of transitive closure for an inner join: Suppose that you have written this query, which meets the conditions for transitive closure:

SELECT * FROM T1, T2
  WHERE T1.C1=T2.C1 AND
  T1.C1>10;

Db2 generates an additional predicate to produce this query, which is more efficient:

SELECT * FROM T1, T2
  WHERE T1.C1=T2.C1 AND
  T1.C1>10 AND
  T2.C1>10;

Example of transitive closure for an outer join

Suppose that you have written this outer join query:

SELECT * FROM                             
  (SELECT T1.C1 FROM T1 WHERE T1.C1>10) X  
  LEFT JOIN                                 
  (SELECT T2.C1 FROM T2)                Y  
  ON X.C1 = Y.C1;

The before join predicate, T1.C1>10, meets the conditions for transitive closure, so Db2 generates a query that has the same result as this more-efficient query:

SELECT * FROM                             
  (SELECT T1.C1 FROM T1 WHERE T1.C1>10) X  
  LEFT JOIN                                 
  (SELECT T2.C1 FROM T2 WHERE T2.C1>10) Y  
  ON X.C1 = Y.C1;

Example of transitive closure for an IN-list predicate

Assume that the following objects exist:

  • Two tables are defined by the following statements:
    CREATE TABLE CAMP 
		(NAME CHAR(128),
		THEME CHAR(64) ,
		LOCATION CHAR(64), 
		...)
    CREATE TABLE STUDENT 
		(NAME CHAR(128), 
		THEME CHAR(64) , 
		LOCATION CHAR(64),
		...)
  • Two indexes are defined by the following statements:
    Index IX_CAMP on CAMP(THEME, LOCATION)
    Index IX_STUDENT on STUDENT(THEME, LOCATION)

Consider the following query:

SELECT C.NAME 'Camp Name', S.NAME 'Student Name'
FROM
CAMP C,
STUDENT S
WHERE
C.THEME = S.THEME
AND C.LOCATION = S.LOCATION
AND S.THEME IN ('theatre', 'jazz')
AND S.LOCATION IN ('monterey', 'carmel')

Through transitive closure, Db2 can generate two predicates to enable more access path options:

SELECT C.NAME 'Camp Name', S.NAME 'Student Name'
FROM
CAMP C,
STUDENT S
WHERE
C.THEME = S.THEME
AND C.LOCATION = S.LOCATION
AND S.THEME IN ('theatre', 'jazz')
AND S.LOCATION IN ('monterey', 'carmel')
AND C.THEME IN ('theatre', 'jazz')
AND C.LOCATION IN ('monterey', 'carmel')

When generating transitive closure for IN list predicates, Db2 sorts the IN-list elements and removes duplicates if the list contains only constants.

Predicate redundancy for transitive closure

A predicate is redundant if evaluation of other predicates in the query already determines the result that the predicate provides. You can specify redundant predicates for transitive closure, or Db2 can generate them. However, Db2 does not determine that any of your query predicates are redundant. Therefore, all predicates that you code are evaluated at execution time, regardless of whether they are logically redundant. In contrast, if Db2 generates a redundant predicate for improved access path selection, Db2 can ignore that predicate at execution.

End program-specific programming interface information.