Predicate Factoring provides significant performance improvement in queries with limited access paths, that is, very few possible SARGs, 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 SARGs. The more SARGs 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, partwhere ((p_partkey = l_partkey and l_quantity >= 10)or (p_partkey = l_partkey and l_quantity <= 20) )
becomes
select * from lineitem, partwhere ((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 SARGs for the optimizer. If new conjuncts (and clauses) added by predicate transformation and factoring are found not to be 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. It is completely transparent to the user. It is implemented as a new compiler phase, just before the start of the optimizer.
When it happens, trace flag 302 output shows additional costing blocks and showplan shows additional “Keys are” messages.
