Home » RDBMS Server » Performance Tuning » Can you fix this query? (oracle 10g)
icon5.gif  Can you fix this query? [message #304027] Mon, 03 March 2008 15:05 Go to next message
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) Shocked
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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,
Re: Can you fix this query? [message #304324 is a reply to message #304185] Tue, 04 March 2008 20:50 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Okay, don't say I didn't warn you.
Previous Topic: Sizing tablespaces
Next Topic: Single operation slows down a query
Goto Forum:
  


Current Time: Tue Dec 03 20:06:34 CST 2024