oracle 递归查询 start with connect by prior 性能优化

NGINX Sprint China 2022--NGINX年度旗舰盛会,已开启免费预约!>>>

oracle 递归查询 start with connect by prior 性能优化

select aa.ufid from utcustomer aa
start with aa.ufparentid in (
select b.res_code
from usms.sec_role_user a
inner join usms.sec_acl_cnmsvip b
on a.role_id = b.role_id
where a.user_iidd = 'vipcust'
and b.oper_id = '30901'
)
connect by prior aa.ufparentid = aa.ufid

上面的sql执行没有任务问题,一旦把上面的sql嵌套在其它语句里就慢得要死,几十条语句查询要90多秒

select RESCODE, RESPARENTCODE, RESNAME from v_customer_res WHERE
resCode in(
select aa.ufid from utcustomer aa
start with aa.ufparentid in (
select b.res_code
from usms.sec_role_user a
inner join usms.sec_acl_cnmsvip b
on a.role_id = b.role_id
where a.user_iidd = 'vipcust'
and b.oper_id = '30901'
)
connect by prior aa.ufparentid = aa.ufid
)

上面的语句执行,数据量只有7条却要耗时90多秒。

create index idx_sec_role_user_role_id on usms.sec_role_user(role_id);

create index idx_sec_acl_cnmsvip_res_code on usms.sec_acl_cnmsvip(role_id,res_code);

试着建立了上面两个索引,也完全没有效果

递归查询用得少,现在网站因为这个sql问题,严重影响用户体验,提问大家有什么办法优化吗,小弟非常感谢!

回答

一般递归查询,我都比较习惯用
with temp as(select/*+ materialize*/)。

回复
@吐槽的达达仔 : 两位的回答太高深了!看不懂materialize hint可以将递归数据存到内存中,不用物理读,只做逻辑读,速度快很多。

有偿服务,可访问http://www.cnblogs.com/zhjh256

今天也是遇到这个问题了,CONNECT BY PRIOR递归计算所有叶子结点,IN查询速度贼慢(拖慢20倍),然后将递归去掉,查询全IN处理,非常快;看到这个帖子,采用WITH XX AS临时处理,速度提高20倍,确实不错。我想几乎所有的这种情况,是不是都可以临时来处理呢?会不会增加Oracle负担?不过确实好用。。