Select OrgCode, YsOrgCode
From (Select A.OrgCode, A.YsOrgCode, A.PluID, A.SupCode, C.CntID
From (Select OrgCode, YsOrgCode, PluID, SupCode
From tOrdPluParasBillMx ) A,
tCntPlu B,
tCntContract C
Where A.PluID=B.PluID And B.CntID=C.CntID And A.SupCode=C.EtpCode
) M
Where Exists (Select 1 From tCntCntOrg K
Where K.CntID=M.CntID
And K.UsOrgCode=M.YsOrgCode)
And Exists (Select 1 From tCntCntOrg S
Where S.CntID=M.CntID
And S.UsOrgCode=M.OrgCode)
上述语句,按编码逻辑,同时满足两个exists子句的结果是存在的,应该能查询出来.但实际情况呢?实际情况是:Oracle将这个语句的条件认为等价于:
Where Exists (Select 1 From tCntCntOrg K
Where K.CntID=M.CntID
And K.UsOrgCode=M.YsOrgCode and K.UsOrgCode=M.OrgCode)
这样,自然判断逻辑就严苛了许多,导致查询不出来应有的结果了.
这儿,说明一下,原来的语句中,K和S两个别名本来都是用的K,出现上述问题后,将之改为一个K,另一个用S,其问题依然存在.(当然,如果将tCntCntOrg表建一模一样的两份,采用不同的表名,则不存在此问题)
看上述语句的执行计划
SELECT STATEMENT, GOAL = ALL_ROWS 76 1 81
NESTED LOOPS 76 1 81
HASH JOIN 75 1 65
HASH JOIN 18 61 3050
TABLE ACCESS FULL HSCMPDEV TORDPLUPARASBILLMX 3 14 476
SORT UNIQUE 14 13637 218192
INDEX FAST FULL SCAN HSCMPDEV PK_TCNTCNTORG 14 13637 218192
VIEW HSCMPDEV index$_join$_005 57 10547 158205
HASH JOIN
INDEX FAST FULL SCAN HSCMPDEV PK_TCNTCONTRACT 34 10547 158205
INDEX FAST FULL SCAN HSCMPDEV IDX_TCNTCONTRACT_ETPCODE 36 10547 158205
INDEX UNIQUE SCAN HSCMPDEV PK_TCNTPLU 1 1 16
但是:自己特意做了个测试小例子,则表现正常.
create table tMain(col1 varchar2(10), col2_1 varchar2(10), col2_2 varchar2(10) );
create table tMain2(col1 varchar2(10), col2_1 varchar2(10), col2_2 varchar2(10) );
create table tRef(col1 varchar2(10), col2 varchar2(10) );
insert into tMain values('1','A','B');
insert into tMain2 values('1','A','B');
insert into tRef values('1','A');
insert into tRef values('1','B');
select col1,col2_1,col2_2
from (select A.col1,A.col2_1,A.col2_2 from tMain A, tMain2 B
where A.col1='1' and A.col1=B.col1 and A.col2_1=B.col2_1
and B.col2_1='A') M
where exists (select 1 from tRef R where R.col1=M.col1 and R.col2=M.col2_1)
and exists (select 1 from tRef R where R.col1=M.col1 and R.col2=M.col2_2);
看来,是oracle的优化器,可能11g做了某些变更,导致此问题的发生.