I have a table of ids which have a parentid in a different table and this builds a folder structure in the application layer. I need to get a list of all of the ids in a specific "Root Folder"
select count(id)
from t1, t2
where t1.id=t2.id
connect by prior t1.id = t2.parentid
start with t1.id in (select id from t3 where name = 'Root Folder')
t1 = 4,102,065 rows
t2 = 48,965,392 rows
t3 is a view
t4 is the underlying table where the name in t3 is stored (only referenced in the explain plan below)
Right now this is returning the correct results, but taking 3m41s (count is 3,257,847)
Is there a better way to do this type of query or are my only options indexes?
Here is the explain plan, I've replaced the real table and index names to match my example above:
Plan
SELECT STATEMENT ALL_ROWSCost: 14 Bytes: 20 Cardinality: 1
17 SORT GROUP BY Bytes: 20 Cardinality: 1
16 CONNECT BY WITH FILTERING
10 NESTED LOOPS Cost: 5 Bytes: 153 Cardinality: 3
8 NESTED LOOPS Cost: 4 Bytes: 27 Cardinality: 1
6 VIEW VIEW SYS.VW_NSO_1 Cost: 2 Bytes: 10 Cardinality: 1
5 HASH UNIQUE Bytes: 57 Cardinality: 1
4 NESTED LOOPS Cost: 2 Bytes: 57 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE T2 Cost: 1 Bytes: 40 Cardinality: 1
1 INDEX RANGE SCAN INDEX T2_NAME Cost: 1 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) T3_ID Cost: 1 Bytes: 17 Cardinality: 1
7 INDEX UNIQUE SCAN INDEX (UNIQUE) T1_ID Cost: 1 Bytes: 17 Cardinality: 1
9 INDEX RANGE SCAN INDEX T2_ID_PARENTID Cost: 1 Bytes: 72 Cardinality: 3
15 NESTED LOOPS Cost: 7 Bytes: 867 Cardinality: 17
13 NESTED LOOPS Cost: 6 Bytes: 1,292 Cardinality: 38
11 CONNECT BY PUMP
12 INDEX RANGE SCAN INDEX T2_PARENTID_ID Cost: 1 Bytes: 312 Cardinality: 13
14 INDEX UNIQUE SCAN INDEX (UNIQUE) T1_ID Cost: 1 Bytes: 17 Cardinality: 1
