测试数据量 TABLE_A 表数据1亿5千万 子查询结果数据量是1百多万
测试环境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.068s
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 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 ) ;
3.---in + union all + distinct --- --两次用时 85.863s 113.805s
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 )) ;
4.--- EXISTS + union all + distinct --- --两次用时8.707s 6.551s
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) ;
|
转播
