测试环境oracle11g 单实例环境
结论: 在大数据量的情况下优先考虑exists,而不是in,exists的性能大于in的性能
1.---in + union --- --两次用时 208.231s 113s
SELECT count(*) FROM TABLE_A WHERE (ACNO,DAT,LOGACNO,SENO,PROVICEID) IN
(
SELECT trim(ACNO) as ACNO,DAT,LOGACNO,SENO,PROVICEID FROM TABLE_R UNION
SELECT trim(ACNO) as ACNO,DAT,LOGACNO,SENO,PROVICEID FROM TABLE_BAL UNION
SELECT trim(ORI_ACNO) as ACNO,ORI_DAT,ORI_LOGACNO,ORI_SENO,PROVICEID FROM TABLE_BAL ) ;
2.---in + union all--- --两次用时 89.496s 109.068sSELECT count(*) FROM TABLE_A WHERE (ACNO,DAT,LOGACNO,SENO,PROVICEID) IN
(
SELECT trim(ACNO) as ACNO,DAT,LOGACNO,SENO,PROVICEID FROM TABLE_R UNION ALL
SELECT trim(ACNO) as ACNO,DAT,LOGACNO,SENO,PROVICEID FROM TABLE_BAL UNION ALL
SELECT trim(ORI_ACNO) as ACNO,ORI_DAT,ORI_LOGACNO,ORI_SENO,PROVICEID FROM TABLE_BAL ) ;
SELECT count(*) FROM TABLE_A WHERE (ACNO,DAT,LOGACNO,SENO,PROVICEID) IN
( SELECT DISTINCT * FROM (
SELECT trim(ACNO) as ACNO,DAT,LOGACNO,SENO,PROVICEID FROM TABLE_R UNION ALL
SELECT trim(ACNO) as ACNO,DAT,LOGACNO,SENO,PROVICEID FROM TABLE_BAL UNION ALL
SELECT trim(ORI_ACNO) as ACNO,ORI_DAT,ORI_LOGACNO,ORI_SENO,PROVICEID FROM TABLE_BAL )) ;
SELECT count(*) FROM TABLE_A A WHERE EXISTS (
SELECT * FROM (
SELECT trim(ACNO) as ACNO,DAT,LOGACNO,SENO,PROVICEID FROM TABLE_R UNION ALL
SELECT trim(ACNO ) as ACNO,DAT,LOGACNO,SENO,PROVICEID FROM TABLE_BAL UNION ALL
SELECT trim(ORI_ACNO) as ACNO,ORI_DAT,ORI_LOGACNO,ORI_SENO,PROVICEID FROM TABLE_BAL ) B
WHERE A.ACNO = B.ACNO AND A.DAT = B.DAT AND A.LOGACNO = B.LOGACNO AND A.SENO=B.SENO AND A.PROVICEID = B.PROVICEID) ;