Improve hierarchical query speed using 'start with' and 'conect by prior'
909500Jan 6 2012 — edited Jan 12 2012Hi
The query within the 'explain' runs about a second and I need to imporve it.
There are indexes set for both the child_id and the parent_id.
The total number of rows for the PRM_COMPONENTS table is 120000.
I'm working on 'Oracle Database 10g Release 10.2.0.4.0 - 64bit Production' in a Linux OS.
I've included the explain plan below.
Any suggestions would be appreciated.
Thanks
EXPLAIN PLAN FOR
SELECT substr(SYS_CONNECT_BY_PATH(usage_title, '|'), 2, instr( SYS_CONNECT_BY_PATH(usage_title, '|'), '|', -1) -2 )
FROM prm_components p
WHERE LEVEL > 1 and usage_id = 10301100
START WITH parent_usage_id is null
CONNECT BY PRIOR usage_id = parent_usage_id;
select * from table(dbms_xplan.display);
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 174 | 4 (0)|
|* 1 | FILTER | | | | |
|* 2 | CONNECT BY WITH FILTERING | | | | |
|* 3 | TABLE ACCESS FULL | PRM_COMPONENTS | 69526 | 3937K| 2468 (1)|
| 4 | NESTED LOOPS | | | | |
| 5 | CONNECT BY PUMP | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| PRM_COMPONENTS | 6 | 174 | 4 (0)|
|* 7 | INDEX RANGE SCAN | PRM_PARENT_USAGE_ID_I | 2 | | 1 (0)|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LEVEL>1 AND "USAGE_ID"=10301100)
2 - access("PARENT_USAGE_ID"=PRIOR "USAGE_ID")
3 - filter("PARENT_USAGE_ID" IS NULL)
7 - access("PARENT_USAGE_ID"=PRIOR "USAGE_ID")
Note
-----
- 'PLAN_TABLE' is old version