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!
