Home » RDBMS Server » Performance Tuning » connect by prior is killing me again (Oracle 10g)
connect by prior is killing me again [message #320646] Thu, 15 May 2008 14:28 Go to next message
zim327
Messages: 15
Registered: October 2007
Junior Member
Ok, I'm trying to find all modified docs in a specific folder and sub folders.
First I found ALL the new or modified docs but once I tried to filter for only docs in a folder and sub folders (i.e. I added the connect by prior) the query fails (after 2.5 hours)
The explain plan revealed a FTS of dtree was the culprit
How can I optimize this query?
SELECT COUNT(a.dataid), c.name 
FROM dauditnew a, dtree b, kuaf c 
WHERE a.auditdate > SYSDATE-10 AND a.auditstr IN ('Create', 'AddVersion') 
AND a.dataid = b.dataid AND c.id = a.performerid  
AND a.SUBTYPE = 0 
START WITH b.dataid = 6132086 CONNECT BY PRIOR a.dataid = b.parentid GROUP BY c.name


Any help will be greatly appreciated
Thanks,
Re: connect by prior is killing me again [message #320648 is a reply to message #320646] Thu, 15 May 2008 14:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions in URL above have you tried & what were the results?


>FROM dauditnew a, dtree b, kuaf c
Why is "dtree b" in the FROM clause when NO data from it contributes to the SELECT clause?

Subordinate DTREE into the WHERE clause

>The explain plan revealed a FTS of dtree was the culprit
but you decided we would not benefit from seeing it.
Re: connect by prior is killing me again [message #320653 is a reply to message #320646] Thu, 15 May 2008 14:44 Go to previous messageGo to next message
zim327
Messages: 15
Registered: October 2007
Junior Member
Here's the explain plan.
I'm still looking over the performance documentation.
Thanks,

P.S. I'm not sure what you mean about dtree b not being in the where clause. It is there -> a.dataid = B.dataid
p.p.s sorry the plan is finally attached
  • Attachment: plan.JPG
    (Size: 84.38KB, Downloaded 483 times)

[Updated on: Thu, 15 May 2008 15:34]

Report message to a moderator

Re: connect by prior is killing me again [message #320688 is a reply to message #320653] Thu, 15 May 2008 21:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Lets break it down:

How long does this take to return EVERY row? And how many rows does it return?
SELECT *
FROM dauditnew a, dtree b, kuaf c 
WHERE a.auditdate > SYSDATE-10 
AND a.auditstr IN ('Create', 'AddVersion') 
AND a.dataid = b.dataid AND c.id = a.performerid  
AND a.SUBTYPE = 0 


Now add the CONNECT BY:
SELECT *
FROM (
    SELECT *
    FROM dauditnew a, dtree b, kuaf c 
    WHERE a.auditdate > SYSDATE-10 
    AND a.auditstr IN ('Create', 'AddVersion') 
    AND a.dataid = b.dataid AND c.id = a.performerid  
    AND a.SUBTYPE = 0
)
START WITH b.dataid = 6132086
CONNECT BY PRIOR a.dataid = b.parentid 

How many rows now? And how long does it take?

Ross Leishman
Re: connect by prior is killing me again [message #320689 is a reply to message #320646] Thu, 15 May 2008 21:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>P.S. I'm not sure what you mean about dtree b not being in the where clause. It is there -> a.dataid = B.dataid

Please read again carefully what I posted...
>FROM dauditnew a, dtree b, kuaf c
Why is "dtree b" in the FROM clause when NO data from it contributes to the SELECT clause?

Subordinate DTREE into the WHERE clause such as
AND a.dataid in (select b.dataid from dtree b......)

>p.p.s sorry the plan is finally attached
& is essentially unreadable.
Re: connect by prior is killing me again [message #320869 is a reply to message #320646] Fri, 16 May 2008 08:54 Go to previous messageGo to next message
zim327
Messages: 15
Registered: October 2007
Junior Member
Thanks for the replies:
Ross:
the first query runs in 579 msecs
the second query throws an error: b.dataid invalid identifier
please advise

Thanks,

Re: connect by prior is killing me again [message #320871 is a reply to message #320646] Fri, 16 May 2008 08:59 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
After the closing paren in the inline view, put a letter x there or something to label the view. Then use x. instead of a. and b. in your start with and connect by lines.
Re: connect by prior is killing me again [message #320872 is a reply to message #320646] Fri, 16 May 2008 09:00 Go to previous messageGo to next message
zim327
Messages: 15
Registered: October 2007
Junior Member
anacedent: I tried your suggestion and it worked!
here's what I did:
SELECT COUNT(a.dataid), c.name, d.name 
FROM dauditnew a, dtree b, kuaf c, kuaf d 
WHERE a.auditdate > SYSDATE-10 AND a.auditstr IN ('Create', 'AddVersion') 
AND a.dataid = b.dataid AND c.id = a.performerid AND d.id = c.groupid 
AND b.dataid IN (SELECT dataid FROM dtree WHERE SUBTYPE = 144 START WITH dataid = 6132086 CONNECT BY PRIOR dataid = parentid) 
GROUP BY c.name, d.name 


you're awesome! never change...

thanks again for everyone's help!

P.s. what format should the explain plan be in?
Re: connect by prior is killing me again [message #320873 is a reply to message #320646] Fri, 16 May 2008 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT COUNT(a.dataid), c.name, d.name 
FROM dauditnew a, kuaf c, kuaf d 
WHERE a.auditdate > SYSDATE-10 AND a.auditstr IN ('Create', 'AddVersion') 
 AND c.id = a.performerid AND d.id = c.groupid 
AND a.dataid IN (SELECT dataid FROM dtree b 
                 WHERE SUBTYPE = 144 START WITH dataid = 6132086 CONNECT BY PRIOR dataid = parentid) 
GROUP BY c.name, d.name 

[Updated on: Fri, 16 May 2008 09:09] by Moderator

Report message to a moderator

Re: connect by prior is killing me again [message #320874 is a reply to message #320646] Fri, 16 May 2008 09:10 Go to previous message
zim327
Messages: 15
Registered: October 2007
Junior Member
smartin:
I tried labeling the view but that doesn't work either
I get an error: column ambigously defined.

thanks,
Previous Topic: Performance Issue on a specific Table only
Next Topic: Oracle NOT IN query is slow
Goto Forum:
  


Current Time: Sat Dec 10 00:55:38 CST 2016

Total time taken to generate the page: 0.16937 seconds