Oracle SQL Join Factorization Transformation (JFT)

Hi All!
Following the line of last week post, let’s talk today about the JFT…

Oracle SQL Join Factorization Transformation (JFT)

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 11g release 2 come with improvements for SQL optimizer on how Oracle handles UNION ALL performance with the new join factorization transformation (JFT). The Join Factorization Transformation applies only to UNION ALL queries.

The following example show how the optimizer improves the performance of UNION ALL by dynamically re-writing a UNION ALL query into a more efficient form using an in-line view:

Original Query:

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;

Re-Written Query:

select t1.c1, VW_JF_1.item_2
     from t1, (select t2.c1 item_1, t2.c2 item_2
                    from t2, t3
                    where t2.c2 = t3.c2 and t2.c2 = 2
               union all
                    select t2.c1 item_1, t2.c2 item_2
                    from t2, t4
                    where t2.c3 = t4.c3) VW_JF_1
     where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;

Nice, right?
Cheers!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.