Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 30 2008
Added on Aug 31 2008
4 comments
696 views