Look ahead predicate generation (LPG)

 

A special type of transitive closure called look ahead predicate generation (LPG) may be costed for joins. In this case, the optimizer attempts to minimize the random I/O costs of a join by pre-applying the results of the query to a large fact table. LPG will typically be used with a class of queries referred to as star join queries, however it can possibly be used with any join query.

Look at the following query:

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

The optimizer may decide to internally modify the query to be:

WITH HT AS (SELECT *
		FROM EMPLOYEE 		WHERE EMPLOYEE.EMPNO='000010')


SELECT * 
	FROM HT, EMP_ACT 	WHERE HT.EMPNO = EMP_ACT.EMPNO 	AND EMP_ACT.EMPNO IN (SELECT DISTINCT EMPNO 				FROM HT)

The optimizer places the results of the "subquery" into a temporary hash table. The hash table of the subquery can be applied in one of two methods against the EMP_ACT (fact) table:

• The distinct values of the hash tables are retrieved. For each distinct value, an index over EMP_ACT is probed to determine which records are returned for that value. Those record identifiers are normally then stored and sorted (sometimes the sorting is omitted, depending on the total number of record ids expected). Once the ids are determined, those subset of EMP_ACT records can then be accessed in a way much more efficient than in a traditional nested loop join processing.

• EMP_ACT can be scanned. For each record, the hash table is probed to see if the record will join at all to EMPLOYEE. This allows for efficient access to EMP_ACT with a more efficient record rejection method than in a traditional nested loop join process.

LPG processing is part of the normal processing in the SQL Query Engine. Classic Query Engine only considers the first method, requires that the index in question by an EVI and also requires use of the STAR_JOIN and FORCE_JOIN_ORDER QAQQINI options.

 

Parent topic:

Join optimization

 

Related reference

Control queries dynamically with the query options file QAQQINI