| connect by prior is killing me again [message #320646] |
Thu, 15 May 2008 14:28  |
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 #320688 is a reply to message #320653] |
Thu, 15 May 2008 21:32  |
rleishman
Messages: 3728
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 #320872 is a reply to message #320646] |
Fri, 16 May 2008 09:00 |
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 |
 |
BlackSwan
Messages: 26766
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
