not in 优化策略

总结网友们在优快云社区上对于not in的优化策略，整理如下，备查。

 

select * from emp where emp_no not in (select emp_no from emp_bill)
要求用两种 SQL 写法优化上面 SQL 。

方法一、

select *

       from emp a

        where   not exists ( select 1

       from emp_bill b

          where b.emp_no = a.emp_no)

方法二、

select a.*

       from emp a ,emp_bill b

       where a.emp_no=b.emp_no(+)

             and b.emp_no is null

方法二继续优化、

select a.*

       from emp a ,emp_bill b

       where a.emp_no=b.emp_no(+)

             and NVL(b.emp_no, '1') = '1'

 

表连接效率最好， Not Exists 其次， Not in 最低

 

实践：

NO_INFO 数据量： 1971

NOTIFY_INFO_BAK 数据量： 138656

 

 

SELECT NO FROM NO_INFO WHERE NO NOT IN
(SELECT DISTINCT NO FROM NOTIFY_INFO_BAK WHERE 1 = 1  
AND CALL_TIME >= TO_DATE( '2009-12-13' , 'yyyy-MM-ss' )
AND CALL_TIME <= TO_DATE( '2010-1-13' , 'yyyy-MM-ss' ))
执行时间： 11.375 秒


SELECT NO FROM NO_INFO A WHERE NOT EXISTS
(SELECT 1 FROM
(SELECT DISTINCT NO FROM NOTIFY_INFO_BAK WHERE 1 = 1  
AND CALL_TIME >= TO_DATE( '2009-12-13' , 'yyyy-MM-ss' )
AND CALL_TIME <= TO_DATE( '2010-1-13' , 'yyyy-MM-ss' )) B WHERE A.NO =B.NO ) AND NVL(A.NO , '1' )!= '1'
执行时间： 11.312 秒

SELECT NO FROM NO_INFO A WHERE NOT EXISTS
(SELECT 1 FROM
(SELECT DISTINCT NO FROM NOTIFY_INFO_BAK WHERE 1 = 1  
AND CALL_TIME >= TO_DATE( '2009-12-13' , 'yyyy-MM-ss' )
AND CALL_TIME <= TO_DATE( '2010-1-13' , 'yyyy-MM-ss' )) B WHERE A.NO =B.NO )
执行时间： 4.313 秒
             
SELECT A.NO FROM NO_INFO A,(SELECT DISTINCT NO FROM NOTIFY_INFO_BAK WHERE 1 = 1  
AND CALL_TIME >= TO_DATE( '2009-12-13' , 'yyyy-MM-ss' )
AND CALL_TIME <= TO_DATE( '2010-1-13' , 'yyyy-MM-ss' )) B WHERE A.NO =B.NO (+) AND NVL(B.NO , '1' )= '1' AND NVL(A.NO , '1' )!= '1'

执行时间： 0.219 秒

 

从上述执行时间，可以很清晰的看到表连接的优势，数据量小时也许差别不大，数据量越大，这种优势就能很好的体现出来了。在平时的开发过程中，要多尝试多测试，不断提升响应速度。