记录一个棵树行的一张表（树的节点5000个左右，级别13级个左右），所以是一个懒加载的树。因为有时候需要实现查找一个父节点下的所有节点。所以在每个表存在一个path，字段记录了这个节点的路径（例如：/爷节点ID/父节点ID）。因为树节点有时候会移动，就涉及到path的批量改动。改动起来好麻烦。所以path就准备不记录，使用视图来生成。但发现效率不是很高，有些没有走索引，求优化：
CREATE OR REPLACE VIEW VIEW_ORG_UNIT_PATH AS
(SELECT "ROW_ID","PATH" FROM(
with
t1 as (select a.row_id,'#' path from md_org_unit a where a.parent_id = '-1'),
t2 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t1 b where a.parent_id = b.row_id),
t3 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t2 b where a.parent_id = b.row_id),
t4 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t3 b where a.parent_id = b.row_id),
t5 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t4 b where a.parent_id = b.row_id),
t6 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t5 b where a.parent_id = b.row_id),
t7 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t6 b where a.parent_id = b.row_id),
t8 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t7 b where a.parent_id = b.row_id),
t9 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t8 b where a.parent_id = b.row_id),
t10 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t9 b where a.parent_id = b.row_id),
t11 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t10 b where a.parent_id = b.row_id),
t12 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t11 b where a.parent_id = b.row_id),
t13 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t12 b where a.parent_id = b.row_id),
t14 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t13 b where a.parent_id = b.row_id),
t15 as (select a.row_id,b.path || b.row_id || '#' path from md_org_unit a,t14 b where a.parent_id = b.row_id)
select ROW_ID,PATH from t1
union all
select ROW_ID,PATH from t2
union all
select ROW_ID,PATH from t3
union all
select ROW_ID,PATH from t4
union all
select ROW_ID,PATH from t5
union all
select ROW_ID,PATH from t6
union all
select ROW_ID,PATH from t7
union all
select ROW_ID,PATH from t8
union all
select ROW_ID,PATH from t9
union all
select ROW_ID,PATH from t10
union all
select ROW_ID,PATH from t11
union all
select ROW_ID,PATH from t12
union all
select ROW_ID,PATH from t13
union all
select ROW_ID,PATH from t14
union all
select ROW_ID,PATH from t15));
以下是执行计划(缩减了部分SQL)：
还是有table FULL access，请问怎么优化，或者有什么其他的修改方案
------解决方案--------------------
直接用connect by start with 构造树形，然后用sys_connect_by_path获取路径就可以了，oracle这方面有现成的语句
------解决方案--------------------
试试这个
select row_id,sys_connect_by_path(row_id,'#') path
from md_org_unit a
start with parent_id = '-1'
connect by prior row_id=parent_id