Oracle WITH clause long running queries
markogAug 31 2008 — edited Sep 2 2008
Hello
I use Oracle BI Server and saw sth strange in my report. I assume that it could happens very often.
I have report with few columns which two of them ar most complicated (many joins subqueries aggreagations on joined values etc.) These two columns (i.e C3,C4) should be multiplied (C3*C4).
When i do pure report without multiplying only columns C1,C2,C3,C4 everything is ok - duration about 15 sec. but... when I put next column on report which multiply these columns (in Answers C5=C3*C4)
I wait 3-4 minutes and my database hungs :(. After investigation I saw that in first case to databese goes pure "SELECT" statement it means:
"Select ... as C1, ... as C2, max(...) as C3, sum(xxx)... C4 from yyy,sss,ttt WHERE joins,filters)"
but in second case BI uses WITH clause it means to database goes:
WITH SAWITH0 AS
( Select ... as C1, ... as C2, max(...) as C3, sum(xxx)... C4 from yyy,sss,ttt WHERE joins,filters)
SELECT SAWITH0.C1 as C1,
SAWITH0.C2 as C2,
SAWITH0.C3 as C3,
SAWITH0.C4 as C4,
SAWITH0.C3SAWITH0.C4 as C5*+_ FROM SSS
and this statement is long runninq query and kills my database :(.
I checked that SQL like this:
Select ... as C1, ... as C2, max(...) as C3, sum(xxx)... C4, max(...)sum(xxx)... As C5*+_ from yyy,sss,ttt WHERE joins,filters" -
runs few times faster than that above
I checked these staetmens in TOAD and statement withe "pure" multiply is much faster than statement using WITH clause. Wher is problem?. Why WITH clause takes so many time?
Explain plans are very different in both cases
Thank you for each kind of help