Connect by prior with changing rules

Question

I have a table like this:

dataid | parentid  |   name
----------------------------
   1   |     99    |  root 
----------------------------
   2   |     -1     | folder1 
----------------------------
   3   |     -1     | folder2 
----------------------------
   4   |    -1    |  file1 
----------------------------
   5   |     2     |  file2

So rules are pretty weird. All of the items in a table are children of root. When folder is inside root it's parentid is negative, when file is inside root it's parentid is negative. But when file or folder is inside folder indise root it's parentid is positive.

So far I've written this query and it works with allways positive parents

SELECT name , CONNECT_BY_ISLEAF "IsLeaf",
   LEVEL, SYS_CONNECT_BY_PATH(dataid, '/') "Path"
   FROM TABLE
   WHERE 1=1
   START WITH dataid = 99
   CONNECT BY PRIOR dataid = abs(parentid);

So, I've managed to make it work using abs. BUT it takes too much time. Is therer another solution or maybe I should create an index...

What happens if you have more than 99 files or folders? Better set `parentid` of "root" to NULL which is more logical to "has no parent". — Wernfried Domscheit, Aug 30 '16 at 10:03
Guys, sorry, changed rules a bit. When something is inside root it's parent is negative, but since level two it's positive. So solution with abs does not work — ifuwannaride, Aug 30 '16 at 10:05
I just tested exactly with your data and with `connect by abs(parentid) = prior dataid`, it works. — Ponder Stibbons, Aug 30 '16 at 10:28
Ok, it works, I found my mistake - I have to exchange dataid and abs(parentid) in CONNECT TO PRIOR clause. Still - takes too much time. — ifuwannaride, Aug 30 '16 at 12:18
Create a functional index on abs(parentid) like so: CREATE INDEX thisidx ON table (abs(parentid)); — sers, Aug 30 '16 at 13:28
How many rows do you have and how do you define "too much time"? — Missy, Aug 30 '16 at 14:51

Connect by prior with changing rules

0 Answers0