Union 和 Union all + distinct 性能比较

 测试数据量查询结果数据量是1百多万
 测试环境oracle11g 单实例环境

 结论：在大数据量的情况下distinct + union all  性能大于 UNION 的性能

1.distinct + union all 场景

 ---数据量1074177--  ----用时2.158秒----
 SELECT COUNT(*) FROM (
 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 ));

2.union场景 

---数据量1074177--  ----用时5.369秒----

SELECT COUNT(*) FROM (
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 );