Home » RDBMS Server » Performance Tuning » Can you fix this query? (oracle 10g)
Can you fix this query? [message #304027] |
Mon, 03 March 2008 15:05 |
zim327
Messages: 15 Registered: October 2007
|
Junior Member |
|
|
Good afternoon,
I wrote this query and can anyone tell me what's wrong with it?
It just hangs there forever (over an hour)
Any help would be greatly appreciated:
SELECT
a.name,
(SELECT ROUND(SUM(dv.DATASIZE)/1073741824,5) || ' GB'
FROM dversdata dv
WHERE dv.docid IN (
SELECT dataid
FROM dtree i
START WITH i.dataid = i.parentid
CONNECT BY PRIOR i.dataid = a.dataid
)
)
FROM dtree a
WHERE a.dataid IN(
6132280,6261409,6261444,7189725,8750043,
6132281,6132091,6131963,6132289,6261709,
9945377,6261787,6131957,8107361,6244885,
6245091,6236059,6237711,6242579,6244894,
6242271,6242589,12410477,6132179,6131958,
6132283,6132088,6132087,6242886,6244027,
6245099,6244330,6246235,12032504,6246105
)
ORDER BY a.name ASC
Thank you,
[query reformateed by moderator]
[Updated on: Mon, 03 March 2008 19:25] by Moderator Report message to a moderator
|
|
|
Re: Can you fix this query? [message #304043 is a reply to message #304027] |
Mon, 03 March 2008 19:40 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
How many rows are returned from dtree?
You do realize that the scalar sub-query in the SELECT clause will be executed once for EVERY row returned. Get a benchmark performance for one execution and multiply it by the expected number of rows returned - that's how long your query will take.
Ross Leishman
|
|
|
Re: Can you fix this query? [message #304044 is a reply to message #304043] |
Mon, 03 March 2008 19:46 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Actually, it's worse than that!
Because of you have correlated the CONNECT BY, the sub-sub-query will execute once for every row in dversdata. And this will all happen once for every row selected from dtree.
Also, I think you have your START WITH and CONNECT BY confused. It probably should be:
START WITH i.dataid = a.dataid
CONNECT BY PRIOR i.dataid = i.parentid
Ross Leishman
|
|
|
Re: Can you fix this query? [message #304045 is a reply to message #304044] |
Mon, 03 March 2008 19:54 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Try this. It's untested code, so you might need to do some work to get it running.
SELECT a.name,
FROM dtree a
LEFT JOIN (
SELECT root_dataid
, ROUND(SUM(dv.DATASIZE)/1073741824,5) || ' GB' AS datasize
FROM dversdata dv
JOIN (
SELECT CONNECT_BY_ROOT dataid AS root_dataid
, dataid
FROM dtree
CONNECT BY PRIOR dataid = parentid
) i
ON dv.docid = i.dataid
GROUP BY root_dataid
) t
ON t.root_dataid = a.dataid
WHERE a.dataid IN(
6132280,6261409,6261444,7189725,8750043,
6132281,6132091,6131963,6132289,6261709,
9945377,6261787,6131957,8107361,6244885,
6245091,6236059,6237711,6242579,6244894,
6242271,6242589,12410477,6132179,6131958,
6132283,6132088,6132087,6242886,6244027,
6245099,6244330,6246235,12032504,6246105
)
ORDER BY a.name ASC
Ross Leishman
|
|
|
Re: Can you fix this query? [message #304185 is a reply to message #304027] |
Tue, 04 March 2008 08:09 |
zim327
Messages: 15 Registered: October 2007
|
Junior Member |
|
|
Hey thanks for all the help!
Here's the working query, if you're interested
It takes at least 10 minutes to run (approximately)
SELECT a.name
--
, ROUND((
SELECT SUM((
SELECT SUM(dv.DATASIZE)
FROM dversdata dv
WHERE dv.docid=dt.dataid))
FROM dtree dt
CONNECT BY PRIOR dt.dataid = dt.parentid
START WITH dt.parentid = a.dataid
)/1073741824,5) || ' GB' AS "size"
FROM dtree a
WHERE a.dataid
IN(
6132280,6261409,6261444,7189725,8750043,6132281,6132091,6131963,6132289,6261709,9945377,6261787,
6131957,8107361,6244885,6245091,6236059,6237711,6242579,6244894,6242271,6242589,12410477,
6132179,6131958,6132283,6132088,6132087,6242886,6244027,6245099,6244330,6246235,12032504,6246105
)
ORDER BY a.name ASC
Best regards,
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 20:06:34 CST 2024
|