I have a view written like
create view v1 as
with conn_query as (xxx)
select stuff from conn_query where some_cond
union
select stuff from conn_query where some_other_cond
But suddenly querying this view became very slow (10 seconds for a result of 2 records), even though the
single queries in the union where very fast (milliseconds). Apparently the union caused xxx to be
fully evaluated without pushing down the arguments (10 seconds is about also the time it takes to select count(*) from v1)
Rewriting the view to
create view v1 as
select stuff from (xxx) where some_cond
union
select stuff from (xxx) where some_other_cond
fixed this. Unfortunately, this makes now the SQL xxx appear twice which is a bit ugly.
So now I'm curious if on a conceptual level, is there a good reason for this behaviour?
This is with Oracle 11.2.0.4.0
UPDATE/DUPLICATE:
I got exactly the expected fast behaviour with the cte variant by using
create view v1 as
with conn_query as (select /*+INLINE*/ xxx)
select stuff from conn_query where some_cond
union
select stuff from conn_query where some_other_cond
as suggested in https://dba.stackexchange.com/a/172559/137386. This undocumented hint prevents Oracle from trying to be smart and materialzing the CTE just because it is accessed more than once.
