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 ?