EXISTS写的SQL:
SELECT ROWNUM,JH,RCYL,RCY,BZ
FROM CYEC_DBA04V1 AWHERE
NY=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')
AND EXISTS (select * from CYEC_DBA_C01LJV B
where A.JH=B.JH
AND RQ>=TO_DATE('2010-11-1','YYYY-MM-DD')
AND RQ<=TO_DATE('2010-11-26','YYYY-MM-DD')
AND DW='105'
AND csmc ='检泵')
上面的SQL语句在PL/SQL中执行不动SELECT distinct A.JH AS 井号,RCYL AS 日产液量,RCY AS 日产油量,A.BZ AS 备注
FROM CYEC_DBA04V1 A,CYEC_DBA_C01LJV B
WHERE NY=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')
AND A.JH(+)=B.JH
AND RQ>=TO_DATE('2010-11-1','YYYY-MM-DD')
AND RQ<=TO_DATE('2010-11-26','YYYY-MM-DD')
AND DW='105'
AND csmc ='检泵'
这个SQL语句可以执行出来,不在资料上看的不是说EXISTS执行的效率比IN高么,为什么在这里执行不出来那。
请哪位高手给解释下
SELECT ROWNUM,JH,RCYL,RCY,BZ
FROM CYEC_DBA04V1 AWHERE
NY=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')
AND EXISTS (select * from CYEC_DBA_C01LJV B
where A.JH=B.JH
AND RQ>=TO_DATE('2010-11-1','YYYY-MM-DD')
AND RQ<=TO_DATE('2010-11-26','YYYY-MM-DD')
AND DW='105'
AND csmc ='检泵')
上面的SQL语句在PL/SQL中执行不动SELECT distinct A.JH AS 井号,RCYL AS 日产液量,RCY AS 日产油量,A.BZ AS 备注
FROM CYEC_DBA04V1 A,CYEC_DBA_C01LJV B
WHERE NY=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')
AND A.JH(+)=B.JH
AND RQ>=TO_DATE('2010-11-1','YYYY-MM-DD')
AND RQ<=TO_DATE('2010-11-26','YYYY-MM-DD')
AND DW='105'
AND csmc ='检泵'
这个SQL语句可以执行出来,不在资料上看的不是说EXISTS执行的效率比IN高么,为什么在这里执行不出来那。
请哪位高手给解释下
应该的有连接
根本就没有绝对 的看子层sql的数据量来根据的子层数据量少就in,相反则exists