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 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