Hi,
I have a SQL where it is filtering tables using inline views and then joining with other tables. I am supposed to tune it. I was thinking directly joining and using where clause would be better rather than WITH clause inline views.
I am using Oracle 11g.
SQL with inline views:
WITH t1 as
(select ... from a1... where ...)
, t2 as
(select ... from a2... where ...)
,t3 as
(select ... from a3... where ...)
,t4 as
(select ... from a4 inner join t3 on (...) left outer join t2 (...) left outer join t2 (...)
where a4...);
SQL without inline views:
select ..
from a4 inner join a3 on (a4...= a3...)
left outer join a2 on (a3... = a2... and a2...=)
left outer join a1 on (a3... = a1... and a1...=)
where a4...=
and a3...=;
Appreciate anyone's expert opinion on this.
Pls note that even though the SQL uses WITH clause, the entire SQL is not using subquery factoring, i.e., the inline views are used only once in the SQL.