oracle connect by很强,但是要慎用,不然有你哭的时候

简介: oracle connect by很强,但是要慎用,不然有你哭的时候

前言:


       第四次工业革命,带来了科技的巨大变更,同时带来了很多半结构化数据,很多数据会做成集合、JSON的形式存储到数据库中,通过ETL工具我们将这些数据抽取到数仓里面,我们怎么进行分析呢?这些数据类似这样的保留在数据库里面。比如下面所示,同一个检测项目由多个人负责检测,因此会通过"\" "/"等等分隔符一次性将数据录入字段里头,方便用户进行数据维护,当然这些数据对分析人员提出较高的要求。为了将这些数据拆分为多行,我们就会使用到connect by来拆分,将数据拆分为多行。

        针对这些数据,我在前面写了一篇文章介绍来处理这些数据,也是因为性能的问题,然后使用存储过程,一条条执行,将一行数据拆分好以后,存储至数据库,直到拆分完毕为止。具体我们可以参考我以前写的文章。


oracle一次性说清楚,多种分隔符的一个字段拆分多行,再多行多列多种分隔符拆多行,最终处理超亿亿。。亿级别数据量_oracle分隔符_他们叫我技术总监的博客-CSDN博客


一、connect by常见用法


1、万年历


代码

SELECT '年' lx, TO_CHAR(ADD_MONTHS(SYSDATE, (4 - ROWNUM)*12), 'YYYY') YEAR_LIST 
FROM DUAL CONNECT BY ROWNUM <= 5 --获取近5年的年份

效果


2、生成序列


代码

select rownum from dual connect by rownum<=10;--生成1-10的序列

效果


3、一行变多行


代码

select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport
from dual connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+');--一行数据拆分为多行

效果


总结:


总的来说,connect by在处理少量的树状数据还是很强大的,这也是很多人喜欢应用它的原因。但是oracle 是不清楚connect by后会出现多少行的数据结构,因此oracle 容易错误的cardinality估算,从而走了NESTED LOOPS,因无法估算结果数据行,因此当原始数据量在500-800行时性能就会变的很差。


二、实战案例剖析


1、union all +connect by


       在我现在的一个项目上就遇到了一个经典的案例,就是因使用了connect by导致,一个数据同步了快3天都没成功,具体为啥会同步3天呢?因刚好是周五下班后用户反馈,然后因下班到家了,不能远程处理,在周日停掉执行后,优化了部分逻辑,再执行,到周一早上发现还是没达到想要的效果。因对应SQ较复杂,就没详细去分析原因。具体SQ如下所示:

select 
       a.state xtstate,
       a.current_nodes_info dbr,
       c.*,
       b.FILENAME,
       b.FILE_URL,
 case when c.field0035  is null then  round(to_date(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
                         'yyyy-mm-dd hh24:mi:ss')-  to_date(to_char(c.START_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                     'yyyy-mm-dd hh24:mi:ss'),2)  --没有审批意见的
 when  FIELD0090  is not null then  round(to_date(to_char(FIELD0090, 'yyyy-mm-dd hh24:mi:ss'),
                         'yyyy-mm-dd hh24:mi:ss')-  to_date(to_char(c.START_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                     'yyyy-mm-dd hh24:mi:ss'),2) end     
 clsc,
       to_char(FIELD0090, 'yyyy-mm-dd hh24:mi:ss') FINISH_DATE,
       to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') etlts,
       case
          when a.FINISH_DATE is not null then
          '关闭'
         when FIELD0090 is  not null  then
          '评审完成'
         when   case when c.field0035  is null then  round(to_date(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
                         'yyyy-mm-dd hh24:mi:ss')-  to_date(to_char(c.START_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                     'yyyy-mm-dd hh24:mi:ss'),2)  --没有审批意见的
 when  FIELD0090  is not null then  round(to_date(to_char(FIELD0090, 'yyyy-mm-dd hh24:mi:ss'),
                         'yyyy-mm-dd hh24:mi:ss')-  to_date(to_char(c.START_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                     'yyyy-mm-dd hh24:mi:ss'),2) end   >= 5 then
          '超期'
         when   case when c.field0035  is null then  round(to_date(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
                         'yyyy-mm-dd hh24:mi:ss')-  to_date(to_char(c.START_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                     'yyyy-mm-dd hh24:mi:ss'),2)  --没有审批意见的
 when  FIELD0090  is not null then  round(to_date(to_char(FIELD0090, 'yyyy-mm-dd hh24:mi:ss'),
                         'yyyy-mm-dd hh24:mi:ss')-  to_date(to_char(c.START_DATE, 'yyyy-mm-dd hh24:mi:ss'),
                     'yyyy-mm-dd hh24:mi:ss'),2) end   < 5 then
          '进行中'
       end psjd,
       mx.*
  from V3XUSER.COL_SUMMARY A
  left join V3XUSER.CTP_ATTACHMENT b
    on a.id = b.SUB_REFERENCE
 right join  (select * from  V3XUSER.formmain_2182 c where to_char(c.start_date,'yyyy-mm-dd')>to_char(sysdate-60,'yyyy-mm-dd') )  c--只更新近2个半月的数据
    on c.id = a.FORM_RECORDID
  left join (select lscs.*,
                    lswc.field0068 wcsj,
                    lswc.field0069 wcqkms,
                    fj.filename    filename2,
                    fj.file_url    file_url2,
                    FIELD0071      结案确认
               from (select '临时措施' lx,
                            lscs.FIELD0055 csxq,
                            listagg(ry.name, '、') within group(order by ry.name) zrr,
                            lscs.FIELD0057 jhwcsj,
                            lscs.fid,
                            lscs.iid
                       from (SELECT distinct  id iid,
                                             formmain_id fid,
                                             REGEXP_SUBSTR(FIELD0056,
                                                           '[^,]+',
                                                           1,
                                                           LEVEL) cf,
                                             a.*
                               FROM V3XUSER.formson_3565 a
                             CONNECT BY REGEXP_SUBSTR(FIELD0056,
                                                      '[^,]+',
                                                      1,
                                                      LEVEL) is not null) lscs
                       left join V3XUSER.ORG_MEMBER ry
                         on lscs.cf = ry.id
                      group by '临时措施',
                               lscs.FIELD0055,
                               lscs.FIELD0057,
                               lscs.fid,
                               lscs.iid) lscs
               left join V3XUSER.formson_3568 lswc
                 on lscs.fid = lswc.formmain_id
                and lscs.csxq = lswc.field0067
               left join V3XUSER.CTP_ATTACHMENT fj
                 on lswc.field0070 = fj.SUB_REFERENCE
             union all
             select lscs.*,
                    lswc.field0073 wcsj,
                    lswc.field0074 wcqkms,
                    fj.filename,
                    fj.file_url    file_url,
                    field0076      结案确认
               from (select '长久措施' lx,
                            lscs.FIELD0058 csxq,
                            listagg(ry.name, '、') within group(order by ry.name) zrr,
                            lscs.field0060 jhwcsj,
                            lscs.fid,
                            lscs.iid
                       from (SELECT  id iid,
                                             formmain_id fid,
                                             REGEXP_SUBSTR(field0059,
                                                           '[^,]+',
                                                           1,
                                                           LEVEL) cf,
                                             a.*
                               FROM V3XUSER.formson_3566 a
                             CONNECT BY REGEXP_SUBSTR(field0059,
                                                      '[^,]+',
                                                      1,
                                                      LEVEL) is not null) lscs
                       left join V3XUSER.ORG_MEMBER ry
                         on lscs.cf = ry.id
                      group by '长久措施',
                               lscs.FIELD0058,
                               lscs.field0060,
                               lscs.fid,
                               lscs.iid) lscs
               left join V3XUSER.formson_3569 lswc
                 on lscs.fid = lswc.formmain_id
                and lscs.csxq = lswc.field0072
               left join V3XUSER.CTP_ATTACHMENT fj
                 on lswc.field0075 = fj.SUB_REFERENCE
             union all
             select lscs.*,
                    lswc.field0078 wcsj,
                    lswc.field0079 wcqkms,
                    fj.filename,
                    fj.file_url    file_url,
                    field0081      结案确认
               from (select '防呆设计' lx,
                            lscs.FIELD0061 csxq,
                            listagg(ry.name, '、') within group(order by ry.name) zrr,
                            lscs.field0063 jhwcsj,
                            lscs.fid,
                            lscs.iid
                       from (SELECT  id iid,
                                             formmain_id fid,
                                             REGEXP_SUBSTR(field0062,
                                                           '[^,]+',
                                                           1,
                                                           LEVEL) cf,
                                             a.*
                               FROM V3XUSER.formson_3567 a
                             CONNECT BY REGEXP_SUBSTR(field0062,
                                                      '[^,]+',
                                                      1,
                                                      LEVEL) is not null) lscs
                       left join V3XUSER.ORG_MEMBER ry
                         on lscs.cf = ry.id
                      group by '防呆设计',
                               lscs.FIELD0061,
                               lscs.field0063,
                               lscs.fid,
                               lscs.iid) lscs
               left join V3XUSER.formson_3570 lswc
                 on lscs.fid = lswc.formmain_id
                and lscs.csxq = lswc.field0077
               left join V3XUSER.CTP_ATTACHMENT fj
                 on lswc.field0080 = fj.SUB_REFERENCE
             ) mx
    on c.id = mx.fid

因为项目初期原始表数据量很小,执行connect by的时候基本还看不到什么性能问题。但是当运行3个月左右数据时,原始数据在600多行左右时,就出现性能问题了,真的是慢的不行。具体我们可以来看下执行日志。

如上图所示,一共410条数据,执行了快11分钟,而且还是卡住不动的。Oh my god!这是什么鬼。通过拆分每段的执行过程发现,性能卡在了最后的"mx"表查询,即union all +connect by那段。


2、connect by 具体卡在哪里了


如上图所示,用户会做3个措施,每个措施会有多个责任人,因此会union all 2遍,将3个措施拼接起来,因为责任人会在一行,因此使用了connect by 来进行拆分为多行。看起来逻辑是没啥问题,但是性能时真不行呀。


单独查询临时措施,拆分后817行数据,耗时了快5分钟,可能还能接受,但是加上union all 后那就是雪上加霜了。

哈哈哈,不久DBA就过来找我了,说带有connect by进程执行了差不多1000分钟,哈哈哈是有点离谱的。


三、解决方案


1、游标执行/分而治之


       如上面所说的connect by 在数据量很小的时候,运行效率还是很高的,因此我们可以采用存储过程,或者将union all 拆分开来执行,然后将数据汇总到一个底表里头。具体如下图所示

定义变量,用游标一行行拆分执行

通过存储过程游标来拆分执行


2、换个思路


其实我们仔细分析下需求,我们发现,使用connect by 是为了将责任人拆分为多行来存储,但是在展示的时候又需要使用listegg将责任人组合在一起来展示。那有没有一种办法直接在展示的时候处理呢?

如上图所示,对应责任人主键使用逗号分离的,是不是和in('A','B','C','D')似曾相识。因此我们在展示的时候,使用类似select name  from BI.Oa_Member where id in ('" + AI2 + "')来获取责任人的名称就ok了,bingo~。希望你下次遇到connect by 相关问题的时候,会对你有所启发~

相关文章
|
存储 Oracle 关系型数据库
ORACLE:根据父id查询所有子孙数据,或者根据子id查询所有父数据(start with connect by prior)
一、需求: 我们在开发中经常遇到一种数据库表的设计:一个表中包含父子信息数据,也就是常说的树形数据. —> 最常见的例子就是省市区一体表,就是通过id、pid、level来进行控制,从而一张表来存储数据.我们进行拿数据的时候,不用再连表拿取,直接通过(start with connect by prior)直接便利就会得到数据.
745 2
ORACLE:根据父id查询所有子孙数据,或者根据子id查询所有父数据(start with connect by prior)
|
2月前
|
Oracle 关系型数据库
oracle的start with connect by prior如何使用 整理
oracle的start with connect by prior如何使用 整理
102 4
|
6月前
|
SQL Oracle 关系型数据库
Connect to Autonomous Database Using Oracle Database Tools
Connect to Autonomous Database Using Oracle Database Tools
61 1
|
Oracle 关系型数据库 数据安全/隐私保护
Oracle 10g ORA-12154: TNS: could not resolve the connect identifier specified 问题解决! 我同事遇到的问题。 用户名/
Oracle 10g ORA-12154: TNS: could not resolve the connect identifier specified 问题解决! 我同事遇到的问题。 用户名/
|
存储 SQL Oracle
Oracle的学习心得和知识总结(五)|Oracle数据库 Connect By 技术详解
Oracle的学习心得和知识总结(五)|Oracle数据库 Connect By 技术详解
343 0
Oracle的学习心得和知识总结(五)|Oracle数据库 Connect By 技术详解
|
Oracle 关系型数据库 数据库
Oracle的学习心得和知识总结(五)|Oracle数据库 Connect By 技术详解
本人CSDN博主 孤傲小二~阿沐,本文《Oracle的学习心得和知识总结(五)|Oracle数据库 Connect By 技术详解》来自于我在CSDN的同名文档
|
Oracle 关系型数据库 数据库