ORACLE in、exists 和 not in、not exists 的效率对比_oracle not exists性能

1、结论

a in b => a 外表、b 内表
1、外表大，用 in；内表大，用 exists。
2、无论那个表大，用 not exists 都比 not in 要快。

1.1 思维导图

2、in、exists

2.1 in

in() 只执行一次，它查出 B表中的所有 id 字段并缓存起来(位于内存中)。之后，检查 A 表的 id 是否与 B 表中的 id 相等，如果相等则将 A 表的记录加入结果集中，直到遍历完 A 表的所有记录

SELECT *
  FROM a
 WHERE a.id IN (SELECT b.ID 
                  FROM b);
执行方式相当于：
SELECT * 
  FROM a,(SELECT b.id FROM b) b
 WHERE a.id = b.id

 
 
1
2
3
4
5
6
7
8

2.2 exists

exists() 会执行 A.length 次，它并不缓存 exists() 结果集，因为 exists() 结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回 false，非空则返回 true。

SELECT *
  FROM a
 WHERE EXISTS(SELECT 1 
                FROM b
               WHERE a.id = b.id);
执行方式相当于：
FOR x IN (SELECT * FROM a) LOOP
  IF EXISTS(SELECT 1 FROM b WHERE b.id = a.id) THEN 
    output THE RECORD;
  END IF;
END LOOP;

 
 
1
2
3
4
5
6
7
8
9
10
11

2.3 综合比较

前提：
若 A 表有 1,0000 条记录，B 表有 100,0000 条记录，那么 exists() 会执行 1,0000 次去判断 A 表中的 id 是否与B表中的id相等。
若 A 表有 1,0000 条记录，B表有 1,0000,0000 条记录，那么 exists() 还是执行 1,0000 次，因为它只执行 A.length次，可见 B 表数据越多，越适合exists() 发挥效果。
再如：
若 A 表有 1,0000 条记录，B 表有 100 条记录，那么 exists() 还是执行1,0000 次，还不如使用 in() 遍历10000*100次，因为 in() 是在内存里遍历比较，而 exists() 需要查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。

3、not in、not exists

如果查询语句使用了not in 那么内外表都进行 全表扫描，没有用到索引； 而 not extsts 的子查询 依然能用到表上的索引。
所以无论那个表大，用 not exists都比not in要快。