Predicates generated through transitive closure

 

For join queries, the query optimizer may do some special processing to generate additional selection. When the set of predicates that belong to a query logically infer extra predicates, the query optimizer generates additional predicates. The purpose is to provide more information during join optimization.

See the following examples:

SELECT * FROM EMPLOYEE, EMP_ACT   WHERE EMPLOYEE.EMPNO = EMP_ACT.EMPNO     AND EMPLOYEE.EMPNO = '000010'

The optimizer will modify the query to be:

SELECT * FROM EMPLOYEE, EMP_ACT   WHERE EMPLOYEE.EMPNO = EMP_ACT.EMPNO     AND EMPLOYEE.EMPNO = '000010'
    AND EMP_ACT.EMPNO  = '000010' 

The following rules determine which predicates are added to other join dials:

• The dials affected must have join operators of equal.

• The predicate is isolatable, which means that a false condition from this predicate omits the row.

• One operand of the predicate is an equal join column and the other is a constant or host variable.

• The predicate operator is not LIKE (OPNQRYF %WLDCRD, or *CT).

• The predicate is not connected to other predicates by OR.

The query optimizer generates a new predicate, whether a predicate already exists in the WHERE clause (OPNQRYF QRYSLT parameter).

Some predicates are redundant. This occurs when a previous evaluation of other predicates in the query already determines the result that predicate provides. Redundant predicates can be specified by you or generated by the query optimizer during predicate manipulation. Redundant predicates with predicate operators of =, >, >=, <, <=, or BETWEEN (OPNQRYF *EQ, *GT, *GE, *LT, *LE, or %RANGE) are merged into a single predicate to reflect the most selective range.

 

Parent topic:

Join optimization