Connect By Prior Optimization

komments

Message from Greg Kellogg <gkellogg@opentext.com> via eLink

I am trying to optimize the following query:

 

select dataid, NAME, CREATEDATE, MODIFYDATE, DCOMMENT, SUBTYPE

from (dtree
where subtype in(145,140,136,557,144,749)

and userid = %1

and dataid in (select dataid from dtree connect by prior dataid = parentid start with parentid = 15516208)

 

I am thinking that this would work:

 

SELECT * FROM (

SELECT dataid, NAME, CREATEDATE, MODIFYDATE, DCOMMENT, SUBTYPE

FROM dtree
WHERE subtype in (145,140,136,557,144,749) and userid = %1

)

START WITH parentid = 15516208

CONNECT BY PRIOR dataid = parentid

 

But I also think I might have to add ParentID to the second SELECT statement which might require a join to KUAF.

Comments?  The client has over 7 million rows in dTree so the first query is timing out on them. 

--

Greg Kellogg

(361) 815-8950

Certified Livelink Administrator

America Services Organization

 

 

Appu_Nair

if dcomment is removed does it work ?