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

昨天写了个视图导致出现报错：网上说是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 是修复了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31369373/viewspace-2642965/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31369373/viewspace-2642965/