exists 和 in的性能比较

论坛 期权论坛 脚本     
匿名技术用户   2020-12-30 05:40   11   0
测试数据量 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) ;

分享到 :
0 人收藏
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

积分:7942463
帖子:1588486
精华:0
期权论坛 期权论坛
发布
内容

下载期权论坛手机APP