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

129次阅读

共计 3104 个字符，预计需要花费 8 分钟才能阅读完成。

写了个视图导致出现报错：网上说是 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%’；

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

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%’;