| 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 #320648 is a reply to message #320646 ] |
Thu, 15 May 2008 14:33  |
anacedent
Messages: 5842
Registered: July 2005
Location: surf meets turf in 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 |
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 19 time(s))
[Updated on: Thu, 15 May 2008 15:34]
|
|
|
| Re: connect by prior is killing me again [message #320688 is a reply to message #320653 ] |
Thu, 15 May 2008 21:32 |
rleishman
Messages: 2629
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 |
anacedent
Messages: 5842
Registered: July 2005
Location: surf meets turf in 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 |
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 |
smartin
Messages: 1758
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 |
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 |
anacedent
Messages: 5842
Registered: July 2005
Location: surf meets turf in 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]
|
|
|
| Re: connect by prior is killing me again [message #320874 is a reply to message #320646 ] |
Fri, 16 May 2008 09:10 |
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,
|
|
|
]
Members
Search
Help
Register
Login
Home
