The With have two distinct propose, that could be combine.
First: make the query more clean (more readable) eg:
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=10
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=15
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=20
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=25
union all
select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14
and tab1.col30=30;
Now compare With
With base_table as
(select col1 alias1,col2 alias2,col3 alias3,col4 alias4,Col5 alias5,col6 alias6,col7 alias 7,col7 alias8, col1+col2 alias1_2
from tab1,tab2,tab3,tab4,tab5,tab6
where
tab1.col10=tab2.col10 and
tab2.col11=tab3.col11 and
tab3.col12=tab4.col12 and
tab4.col13=tab5.col13 and
tab5.col14=tab6.col14)
select * from base_table where tab1.col30=10
union all
select * from base_table where tab1.col30=15
union all
select * from base_table where tab1.col30=20
union all
select * from base_table where tab1.col30=25
union all
select * from base_table where tab1.col30=30
I know that in both cases i could use OR but it´s just to ilustrate
So Oracle will have two diferents behaviours,
If selectivity is bad , than Oracle will simply expand the base_table as if was a view.
But if selectivity is good than Oracle execute just once, and give you only the result set.
Regards
Helio Dias
http://heliodias.com
