oracle使用CONNECT BY LEVEL查询速度慢的问题

最新推荐文章于 2024-08-26 15:53:21 发布

烟雨晓笙

最新推荐文章于 2024-08-26 15:53:21 发布

阅读量541

点赞数 2

文章标签： oracle 数据库

原文链接：https://blog.csdn.net/qq_39255840/article/details/134269109

版权

一、问题
oracle 使用regexp_substr+CONNECT BY LEVEL来，根据特定字符拆分成多行。
（注意这里我的数据是每个值都有“ ; ”，即使后面没有值，后面也会有个“ ; ”，如果是正常的分隔符，sql 需要改成” LEVEL < = regexp_count(name, ‘;’)+1 ”）

select distinct regexp_substr(name, '[^;]+', 1, LEVEL) name
from (select distinct name
from table_name a
where TIME1 > sysdate - 1 / 24)
CONNECT BY LEVEL < = regexp_count(name, ';');
执行后发现特别慢，查询了好几分钟没没出来。
如果单独拿select distinct name
from table_name a
where TIME1 > sysdate - 1 / 24
查询，发现挺快的。
查看下执行计划

二、解决方法
调整后。通过利用rownum 进行join ，sql：

select distinct regexp_substr(name, '[^;]+', 1, n) name
from (select
distinct name, regexp_count(name, ';') rn
from table_name a
where TIME1 > sysdate - 1)
join (SELECT rownum n from table_name a where rownum <= 999)
on n <= rn
(rownum <= 999可以改成rownum <=(select max(regexp_count(name, ‘;’)) from table_name where TIME1 > sysdate - 1) )
注意：
这个里table_name表的总行数，需要比(select max(regexp_count(name, ‘;’)) from table_name where TIME1 > sysdate - 1) 大。如果没有，随便拿个比较大的表替代，不可使用(SELECT LEVEL n FROM DUAL CONNECT BY LEVEL <= 99)替代。

执行计划：

查询耗时只需要2s多
三、总结
CONNECT BY是层次查询，一般用来构造树形的构造，这里查询不太适用。通过rownum构造虚拟的表，进行jion查询，执行计划就会执行MERGE JOIN 进行关联，效率将会大大提高。

原文链接：https://blog.csdn.net/qq_39255840/article/details/134269109