Hi,
I have a slow query problem...
The query is basically like...
SELECT A.Blah, B.Stuff
FROM TABLE1 A,
(SELECT MAX(C.StringValue) FROM TABLE2 C,TABLE3 D WHERE GROUP BY D.IDVal) B
WHERE A.Blah > 3
GROUP BY
A.Blah
B.Stuff
ORDER BY
A.Blah
It's the sub query that's causing the problem, when I take out the subquery, it runs fine, if I run the subquery on it's own, it runs fine, but the two together take up to 60 seconds to return 243 rows... Here's the execution path...
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 21 60.57 60.58 3917 198287 11 293
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 60.58 60.59 3917 198287 11 293
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 43
Rows Row Source Operation
------- --------------------------
----------
----------
-----
293 SORT GROUP BY
1170 NESTED LOOPS
1171 NESTED LOOPS
1171 NESTED LOOPS
34285 NESTED LOOPS
34285 NESTED LOOPS
294 VIEW
294 SORT GROUP BY
1170 MERGE JOIN
249 SORT JOIN
248 TABLE ACCESS FULL TABLE2
1170 SORT JOIN
1170 TABLE ACCESS FULL TABLE3
34577 TABLE ACCESS BY INDEX ROWID TABLE3
34577 INDEX RANGE SCAN (object id 3145)
68568 TABLE ACCESS BY INDEX ROWID TABLE2
68568 INDEX UNIQUE SCAN (object id 3034)
35454 TABLE ACCESS BY INDEX ROWID TABLE1
35454 INDEX UNIQUE SCAN (object id 3201)
2340 TABLE ACCESS BY INDEX ROWID TABLE4
2340 INDEX UNIQUE SCAN (object id 3031)
1170 TABLE ACCESS BY INDEX ROWID TABLE5
2340 INDEX UNIQUE SCAN (object id 3165)
**************************
**********
**********
**********
**********
**********
****
Can anyone see what I'm missing here? I think I just need to use that subquery differently...
Start Free Trial
