Predicate factoring provides significant performance improvement in queries with limited access paths, which include queries with few possible search arguments, joins, or or clauses that can be used to qualify rows in a table.
Additional optimization is achieved by generating new search paths based on join conditions, search clauses, and optimizable or clauses.
Predicate factoring helps query optimization by extracting optimizable clauses from predicates linked with or (difficult to optimize) and replacing them with and clauses (easier to optimize), thus providing the optimizer with more usable search arguments. The more search arguments available, the more information the optimizer has, and the more likely it is to chose an efficient plan.
Full Cartesian joins are avoided for some complex queries.
Example:
select * from lineitem, part where ((p_partkey = l_partkey and l_quantity >= 10) or (p_partkey = l_partkey and l_quantity <= 20) )
becomes
select * from lineitem, part where ((p_partkey = l_partkey and l_quantity >= 10) or (p_partkey = l_partkey and l_quantity <= 20) ) and (p_partkey = l_partkey) and (l_quantity >= 10 or l_quantity <= 20)
The addition of the conjuncts adds usable search arguments for the optimizer. If new conjuncts (and clauses) added by predicate transformation and factoring are found to be not useful as part of index access strategy (that is, for filtering) they are not used.
Query semantics do not change; the result set is the same. Predicate factoring cannot be turned off, and is completely transparent to the user. It is implemented as a new compiler phase, just before the start of the optimizer.
When it is implemented, trace flag 302 output shows additional costing blocks and showplan shows additional “Keys are” messages.
