SQL优化-关联查询不要用not in

首先，我们看not in写法及执行计划

SELECT count(*)

  FROM test t1

 WHERE  1234

         NOT IN (SELECT t2.object_id

                   FROM test  t2

                  WHERE t1.owner = t2.owner

                                and t1.object_name=t2.object_name);

执行计划走了Filter连接，我们加unnest hint也无法消除Filter。

用not exists改写后

SELECT count(*)

  FROM test t1

 WHERE not exists (SELECT 1

                   FROM test  t2

                  WHERE t1.owner = t2.owner

                                and t1.object_name=t2.object_name

                                   and t1.object_id=1234);

Filter被自动消除。