OraFAQ Forum: Performance Tuning » Can you fix this query?

Home » RDBMS Server » Performance Tuning » Can you fix this query? (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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) 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

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

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

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,

Report message to a moderator

Re: Can you fix this query? [message #304324 is a reply to message #304185]

Tue, 04 March 2008 20:50

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Okay, don't say I didn't warn you.

Report message to a moderator

Previous Topic:	Sizing tablespaces
Next Topic:	Single operation slows down a query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 21:07:50 CDT 2024