Oracle SQL Join Factorization Transformation (JFT) Tips

The UNION operator is great for merging the results of multiple queries that return similar rowsets, essentially executing each query separately and merging the results together into a single result set. 

Oracle continues to improve the SQL optimizer with each new release and Oracle has improved how Oracle handles UNION ALL performance in Oracle 11g release 2 with the new join factorization transformation (JFT).  The Join Factorization Transformation applies only to UNION ALL queries.

The Oracle CBO Group gives this example on how the optimizer now improves the performance of UNION ALL by dynamically re-writing a UNION ALL query into a more efficient form using an in-line view:

Q1:     
    select t1.c1, t2.c2     
    from t1, t2, t3    
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2   
union all    
    select t1.c1, t2.c2    
    from t1, t2, t4    
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;

Table t1 appears in both the branches. As does the filter predicates on t1 (t1.c1 > 1) and the join predicates involving t1 (t1.c1 = t2.c1). Nevertheless, without any transformation, the scan (and the filtering) on t1 has to be done twice, once per branch. Such a query may benefit from join factorization which can transform Q1 into Q2 as follows:

In Q2, t1 is "factorized" and thus the table scan and the filtering on t1 is done only once (it's shared). If t1 is large, then avoiding one extra scan of t1 can lead to a huge performance improvement.

As we see the Join Factorization Transformation simply transforms simple UNION ALL queries into in-line views, thereby avoiding an extra full-table scan. Again, the Join Factorization Transformation query re-write is complete transparent, and you do not need to do anything at all to utilize this feature in Oracle 11gR2.