Oracle10.2中,同一个语句执行结果不同,不同的结果出现时机几率也不确定,多数出现在一个新会话中。如图所示。第一次执行时的结果是不正确的,第二次结果是正确的,可以保证涉及的源数据没有发生变化。该语句在相同的会话里再次执行,多数结果都是正确的(32个记录),偶尔会出现错误结果(864个记录)。是否相同的sql执行计划不一致,即便计划不一致,结果也不能不同呀。请同学帮忙看看,提供一些思路。
SQL> INSERT INTO T_LHDJ_YHFT_MN(XSRQ,DWBM,JYFS,XSJE,FTBL,FTJE)
2 SELECT '20220714' ,X.DWBM,X.JYFS,SUM(X.XSJE),Y.SJBL,ROUND(SUM(X.XSJE)*Y.SJBL/100,2)
3 FROM T_SKMX X ,T_YHFTBL Y
4 WHERE (SKJBM,LSH,XSBZ) IN (SELECT SKJBM,LSH,XSBZ FROM T_FSSRMX WHERE XSRQ='20220714'
5 AND FSBM IN ('48','75','76'))
6 AND X.DWBM=Y.DWBM AND '20220714' BETWEEN Y.QSRQ AND Y.ZZRQ
7 AND X.XSRQ='20220714' AND NOT EXISTS(SELECT 1 FROM T_YHFTBL
8 WHERE SPBM=X.SPBM AND '20220714' BETWEEN QSRQ AND ZZRQ AND FTLB='联华到家')
9 AND Y.FTLB='联华到家' GROUP BY X.DWBM,X.SPBM,X.JYFS,Y.SJBL;
EXPL_PLAN
---------------------------------------------------------------
INSERT STATEMENT Cost:863
HASH GROUP BY
NESTED LOOPS ANTI
NESTED LOOPS
MERGE JOIN CARTESIAN
TABLE ACCESS BY INDEX ROWID T_YHFTBL TABLE
INDEX RANGE SCAN I_YHFTBL_RQ INDEX
BUFFER SORT
SORT UNIQUE
TABLE ACCESS BY INDEX ROWID T_FSSRMX TABLE
INDEX RANGE SCAN I_FSSRMX_XSRQ INDEX
TABLE ACCESS BY INDEX ROWID T_SKMX TABLE
INDEX RANGE SCAN I_SKMX_XSRQ INDEX
TABLE ACCESS BY INDEX ROWID T_YHFTBL TABLE
INDEX RANGE SCAN I_YHFTBL_RQ INDEX
查询到15记录.