请教这个递归查询如何优化

显示全部楼层 · 2008-10-24 13:26:02

我的一个递归查询如下,递归查询的表也就1000多行,但执行出来却花了240多秒,请教下怎么优化!!!

select distinct mtt.taskitem_id

from security_taskitem mtt,

(

select mrt.task_id

from security_userrole mur,

security_role mrt

where mur.user_id = 2

and mur.role_id = mrt.role_id

) temp

connect by mtt.task_id = prior mtt.taskitem_id

start with mtt.task_id in(temp.task_id)
执行计划
SELECT STATEMENT, GOAL = CHOOSE

SORT UNIQUE

CONNECT BY WITHOUT FILTERING

COUNT

NESTED LOOPS

NESTED LOOPS

TABLE ACCESS FULL
SECURITY_USERROLE

TABLE ACCESS BY INDEX ROWID
SECURITY_ROLE

INDEX UNIQUE SCAN
PK_SECURITY_ROLE

TABLE ACCESS FULL
SECURITY_TASKITEM

千问 · 2008-10-24 13:26:02

数据库什么版本，为何使用RBO（表无统计信息）

千问 · 2008-10-24 13:26:02

mur.user_id = 2这一步没走上索引,不知道表的记录数以及mur.user_id = 2的记录数,

千问 · 2008-10-24 13:26:02

试一试吧！不知如下的语句是否等效?
select taskitem_id
from (select mtt.security_taskitem,

mtt.task_id mtt_task_id,

mtt.taskitem_id,

temp.task_id temp_task_id

from security_taskitem mtt,

(select mrt.task_id

from security_userrole mur, security_role mrt

where mur.user_id = 2

and mur.role_id = mrt.role_id) temp
where mtt.task_id = temp.task_id)
start with mtt_task_id = temp_task_id
connect byprior taskitem_id = mtt_task_id
group by taskitem_id;

千问 · 2008-10-24 13:26:02

非常感谢各位的建议
我这样改了下就快了些
select mtt.taskitem_id

from security_taskitem mtt

connect by mtt.task_id = prior mtt.taskitem_id

start with mtt.task_id in(

select mrt.task_id

from security_userrole mur,

security_role mrt

where mur.user_id = 2

and mur.role_id = mrt.role_id

)
可能原因就是递归表不要和别的表连接去查