手机版
你好,游客 登录 注册
背景:
阅读新闻

Oracle 10.2.0.4 sql关联查询语句中含有 connect by 导致报错出现ORA-00600

[日期:2019-05-03] 来源:itpub.net/31369373  作者:linmenglan1 [字体: ]

写了个视图导致出现报错:网上说是Oracle 10.2.0.4和10.2.0.3版本的一个bug

SELECT A.*FROM  PL_PLAN_BASE  A
      left JOIN
        (SELECT B.CATEGORY_ID,
              REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( B.CLASS_NAME,'/'),'[^/]+',1,1,'i')  FIRST_NAME,
              REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( B.CLASS_NAME,'/'),'[^/]+',1,2,'i')  SECOND_NAME,
              B.CLASS_NAME                                                          THIRD_NAME
        FROM  PC_CATEGORY_BASE B
        START WITH B.PARENT_ID IS NULL
        CONNECT BY  B.PARENT_ID=PRIOR B.CATEGORY_ID  ) C  ON  A.CATEGORY_ID=C.CATEGORY_ID 
        LEFT JOIN
        (SELECT DEPT_NAME,DEPT_CODE FROM  BI_DEPT ) D ON D.DEPT_CODE=A.APPLY_DEPT_CODE
        LEFT JOIN
        (SELECT  T.CATEGORY_ID,D.DEPT_NAME,T.ORG_CODE
          FROM  PC_CATEGORY_ORG T,BI_DEPT D
          WHERE  T.CENTRALIZED_DEP_CODE=D.DEPT_CODE
            )E  ON (E.CATEGORY_ID =a.Category_Id  AND E.ORG_CODE=A.CRT_ORG_CODE)
        where a.data_state='0'
          and a.plan_org_name not like '%测试%'
          and a.plan_material_name not like '%测试%' 
          and a.crt_org_name not like '%null%';

1  如果关联语句不是很多是不会报错:比如下面这样是可以查出来 

SELECT A.*FROM  PL_PLAN_BASE  A
      left JOIN
        (SELECT B.CATEGORY_ID,
              REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( B.CLASS_NAME,'/'),'[^/]+',1,1,'i')  FIRST_NAME,
              REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( B.CLASS_NAME,'/'),'[^/]+',1,2,'i')  SECOND_NAME,
              B.CLASS_NAME                                                          THIRD_NAME
        FROM  PC_CATEGORY_BASE B
        START WITH B.PARENT_ID IS NULL
        CONNECT BY  B.PARENT_ID=PRIOR B.CATEGORY_ID  ) C  ON  A.CATEGORY_ID=C.CATEGORY_ID 
        LEFT JOIN
        (SELECT DEPT_NAME,DEPT_CODE FROM  BI_DEPT ) D ON D.DEPT_CODE=A.APPLY_DEPT_CODE 

2 如果关联语句太多就会报错 比如上面的. 

3 解决办法: 

1 修改SQL语句,不要这个递归,去掉这个connect by ; 

2 这么修改,修改这个参数调整优化器的版本:  alter session set optimizer_features_enable='10.2.0.1'; 

 3 修改这个参数:_optimizer_connect_by_cost_based 为 false; 

 我们这边是针对本session的进行语句级修改,只针对这个语句,所以不影响整个库: 

ALTER SESSION SET "  _optimizer_connect_by_cost_based  "=false; 

我这边选择第三种,但是我们这个是要经常查询的,我可以直接加到 hint 里面去,就可以: 

CREATE OR REPLACE VIEW V_TW_PURCHASE_PL_STAT1 AS 

 SELECT  /*+ OPT_PARAM('_optimizer_connect_by_cost_based' 'false') */ 
        A.PLAN_ID,                                                                               
        DECODE(A.PLAN_TYPE,'0','年初计划','1','中期调整',A.PLAN_TYPE)          PLAN_TYPE,       
        A.PLAN_YEAR,                                                                             
        A.CATEGORY_ID,                                                                           
        C.FIRST_NAME,   
        .................................
        FROM  PL_PLAN_BASE  A
      left JOIN
      (SELECT B.CATEGORY_ID,
              REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( B.CLASS_NAME,'/'),'[^/]+',1,1,'i')  FIRST_NAME,
              REGEXP_SUBSTR(SYS_CONNECT_BY_PATH( B.CLASS_NAME,'/'),'[^/]+',1,2,'i')  SECOND_NAME,
              B.CLASS_NAME                                                          THIRD_NAME
        FROM  PC_CATEGORY_BASE B
        START WITH B.PARENT_ID IS NULL
        CONNECT BY B.PARENT_ID=PRIOR B.CATEGORY_ID  ) C  ON  A.CATEGORY_ID=C.CATEGORY_ID
        LEFT JOIN
        (SELECT DEPT_NAME,DEPT_CODE FROM  BI_DEPT ) D ON D.DEPT_CODE=A.APPLY_DEPT_CODE
        LEFT JOIN
        (SELECT  T.CATEGORY_ID,D.DEPT_NAME,T.ORG_CODE
          FROM  PC_CATEGORY_ORG T,BI_DEPT D
          WHERE  T.CENTRALIZED_DEP_CODE=D.DEPT_CODE
            )E  ON (E.CATEGORY_ID =a.Category_Id  AND E.ORG_CODE=A.CRT_ORG_CODE)
        where a.data_state='0'
          and a.plan_org_name not like '%测试%'
          and a.plan_material_name not like '%测试%'
          and a.crt_org_name not like '%null%';

再次查询就不会报错:可以查出来。 

这边是针对该版本,后面的版本ORACLE 是修复了 

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址https://www.linuxidc.com/Linux/2019-05/158456.htm

linux
相关资讯       ORA-00600 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款