ORA-00600: 内部错误代码, 参数: [kkocxj : pjpCtx], [], [], [], [], [], [], []
出错SQL
--SELECT * FROM TPVA_SETT_RULE_GT
INSERT INTO TPVA_SETT_RULE_GT
(REC_SEQ,
ZONE_CODE,
.......................
SETT_RULE_DESC
)
SELECT R.REC_SEQ,
R.ZONE_CODE,
......................
R.SETT_RULE_DESC
FROM TPVA_SETT_RULE R,
(SELECT B.*,
SUM(DECODE(JIETI_CALC_TYPE,
'5VB',
JIETI_PARAM,
(JIETI_END - JIETI_BEGIN) * JIETI_PARAM)) --5VB为固定值,5VA为百分比
OVER(PARTITION BY JIETI_ID ORDER BY JIETI_END) - decode(JIETI_CALC_TYPE, '5VB', JIETI_PARAM, (JIETI_END - JIETI_BEGIN) * JIETI_PARAM) ACCUM --构造增量值,每段阶梯的值加上上一阶梯算出的增量值,就是金额落在当前阶梯段的总金额
FROM (
SELECT REC_SEQ JIETI_REC_SEQ,
JIETI_ID,
JIETI_CALC_TYPE,
JIETI_PARAM,
JIETI_ACCUM,
LAG(JIETI_THRESHOLD, 1, 0) OVER(PARTITION BY JIETI_ID ORDER BY JIETI_THRESHOLD) JIETI_BEGIN --根据阶梯阀值构造出起始阶梯jieti_begin,如0-100的0
,
JIETI_THRESHOLD JIETI_END --将阶梯阀值当成是jieti_end,如0-100的100
,
COUNT(*) OVER(PARTITION BY JIETI_ID) JIETI_CNT,
ROW_NUMBER() OVER(PARTITION BY JIETI_ID ORDER BY JIETI_THRESHOLD) JIETI_ROW --构造jieti_row仅仅是为了后续验证单阶梯只能有一个阶梯段,即0-9999999,如果出现0-100,100-200等就要出错!
FROM TPVA_SETT_JIETI A
WHERE A.EFF_DATE < A.EXP_DATE
-- AND v_Cycle_Date >= EFF_DATE
--AND v_Cycle_Date <= EXP_DATE --配置表中的eff_date和exp_date不一定要在当前有效时间内,比如当前月是10月,我们传入5月账期,规则中恰好有符合5月范围的,就是处理5月时的规则
) B) JT
WHERE R.JIETI_ID = JT.JIETI_ID(+) --外连接
-- AND R.EFF_DATE < R.EXP_DATE
--AND v_Cycle_Date >= R.EFF_DATE
-- AND v_Cycle_Date <= R.EXP_DATE
-- AND R.State = GC_STATE_EFF
AND R.ZONE_CODE = 4100001;
-- SELECT * FROM TPVA_SETT_JIETI
[@more@]经确认为bug。
BUG描述:
Description
Internal errors or wrong results can occur during optimization of queries
with nested views and outer joins if join predicate push is attempted.
An ORA-600[kkocxj:pjpctx] has been raised If group by is present in one of the nested views.
Workaround
Disable cost-based join predicate push into views.
eg: Set _OPTIMIZER_PUSH_PRED_COST_BASED = false
Please
note: The above is a summary description only. Actual symptoms can
vary. Matching to any symptoms here does not confirm that you are
encountering this problem. For questions about this bug please consult
Oracle Support.
解决方法:
alter system Set _OPTIMIZER_PUSH_PRED_COST_BASED = false
Oracle GCS更推荐通过应用补丁7014646的方法来解决问题,而修改以上隐式参数则不一定百分之百能解决问题。
参考:文档9671977.8
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10896118/viewspace-1054737/,如需转载,请注明出处,否则将追究法律责任。