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:
Q2:
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;
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.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
