Thanks for the question, AJO.
Asked: December 15, 2016 - 6:15 am UTC
Last updated: December 16, 2016 - 2:17 am UTC
Version: 12+
Viewed 1000+ times
You Asked
I've seen in one of the client, the practice of writing the similar query as multiple layers of unions
select table1.col_list, table2.col_list
from table1, table2
where table1.col1 =table2.col1
union all
select table1.col_list, table2.col_list
from table1, table2, table3
where table1.col1 =table2.col1
and table1.col1= table3.col1 and predicates..
union all
select table1.col_list, table2.col_list,table4.col_list
from table1, table2, table4
where table1.col1 =table2.col1
and table1.col1= table4.col1 and predicates .....
So, if we look at, I thought of having the commonalities to be materialized in a with statement and the odd ones to be joined later in the respective queries with their own predicates. something like this
with mycore_table as
(select col1, col2, col3
from table1, table2
where table1.col1 =table2.col1)
select mycore_table.col_list from mycore_table where predicates
union all
select mycore_table.col_list , table2.col_list, table3.col_list from mycore_table ,table3 where predicates
union all
select mycore_table.col_list , table2.col_list, table4.col_list from mycore_table ,table4 where predicates
Please not the change of table3, table4 in each union query
I took the plan cost and it was 586792 which reduced to 1562 for the same results. When I highlighted on this, the responses from the client was that " Promote approach that are supported by oracle (oracle support/ oracle documentation)."
Could you share your thoughts ?
and Connor said...
The client is wrong.
The two queries might be executed *differently* by the optimizer, in which case, you would simply use the one that works best.
But dont rely on the plan cost - the best bet is to actually *run* them - and pick the most efficient one.
Is this answer out of date? If it is, please let us know via a Comment