oracle 12c 查询优化,Oracle Database 12c查询优化器的缺陷-视图合并会造成查询结果不准确...

最近在将一个11g的数据库导入到12c(12.1.0.2，并打了最新的补丁)的库后，测试人员反馈有一个SQL执行结果不正确。

具体的SQL如下：

SELECT *

FROM ( SELECT watnum,

agentcode,

managecom,

SUM (prem) AS prem,

SUM (charge) AS charge

FROM (SELECT ct.card_number,

ct.card_managecode,

cp.riskcode,

cp.riskname,

cp.prem,

csm.watnum,

csm.charge,

SUBSTR (csm.agentcode, 2) AS agentcode,

SUBSTR (csm.managecom, 2) AS managecom

FROM card_table@webapp ct,

ZZHCardSettleMent csm,

CARDPREMIUM@WEBAPP cp

WHERE ct.card_type = cp.cardtype

AND csm.idcard = ct.card_number

AND csm.checktype = 1

AND ct.card_managecode = ‘8602’)

GROUP BY watnum, agentcode, managecom) c

WHERE NOT EXISTS

(SELECT ‘Y’

FROM policyinfo

WHERE c.watnum = proposalcontno);

由于第一个子查询(我们姑且把它称为C子查询)涉及到DBLINK，所以一开始怀疑DBLINK不一致导致的。但是查看了DBLINK的配置后发现和源11g的配置是一样的，连的都是相同的数据库。

试着单独，在11g和12c中的执行结果是一样的！

接着试着把代替

SELECT *

FROM ctemp c

WHERE NOT EXISTS

(SELECT ‘Y’

FROM policyinfo

WHERE c.watnum = proposalcontno);

SELECT *

FROM cvtemp c

WHERE NOT EXISTS

(SELECT ‘Y’

FROM policyinfo

WHERE c.watnum = proposalcontno);

SELECT /*+ NO_MERGE(c) */ *

FROM cvtemp c

WHERE NOT EXISTS

(SELECT ‘Y’

FROM policyinfo

WHERE c.watnum = proposalcontno);

alter system set “_complex_view_merging”=false;