I have a query that looks like below and the tables A,T,S have around 1 million rows whereas P have more than 100 million rows. I've newly introduced the inline view "temp" into this query and it caused a drastic degradation in performance. The data retrieved for temp is hardly 50 rows and this inline query runs in a snap when executed alone.
The autotrace statistics show a huge increase in the number of "consistent gets" from a 6 digit number before introducing temp to a 9 digit number after adding this!! Also, more than 90% of LAST_CR_BUFFER_GETS are accounted for the "temp" view. If I extract the data from this view into a temporary table and use that table as part of the joins the performance is very good but that solution is not really feasible for me.
I know the question is very generalized but I'm wondering if there is anything trivially wrong in using this inline view. Doesn't inline views give the same performance like having this data in a temporary table? Is there any way I can hint Oracle to use this view in a effective manner and thus increasing performance.
select t.id,
a.date
from A a,
T t,
P p,
S s,
(select id
from S,
R
where s.id = r.id
and r.code = 10
r.code1 = 20
r.name = 'string1' ) temp
where ...cond1
...cond2
...cond2
s.id = temp.id