欢迎来到HugNew-拥抱变化,扫一扫右边二维码关注微信订阅号:Martin说 或 加QQ群:427697041互相交流,Stay hungry, Stay foolish.

IN/EXISTS子查询的区别及其优化方式

oracle Martin 2076℃ 0评论
在ORACLE 11G大行其道的今天,还有很多人受早期版本的影响,记住一些既定的规则,
   1.子查询结果集小,用IN
   2.外表小,子查询表大,用EXISTS

这是完全错误的观点。在8i时代,这经常是正确的,但是现在已经11G了,马上12C就要面世了。其实在ORACLE 9i CBO就已经优化了IN,EXISTS的区别,ORACLE优化器有个查询转换器,很多SQL虽然写法不同,但是ORACLE优化器会根据既定规则进行查询重写,重写为优化器觉得效率最高的SQL,所以可能SQL写法不同,但是执行计划却是完全一样的。

IN与EXISTS有一点要记住:IN一般是用于非相关子查询,而EXISTS一般用于相关子查询。当然IN也可以用于相关子查询,EXISTS也可以用于非相关子查询。但是这区别很重要,虽然优化器很强大,但是查询转换是有一定的限制的,在EXISTS性能低下,无法进行相关查询转换,比如不能UNNEST SUBQUERY,那么可能我们需要改写SQL,通常可以用IN/JOIN等改写相关子查询,或对于含有OR的SEMI JOIN改为UNION ALL/UNION的形式。

下面就用例子说明一下:

DROP TABLE a;
DROP TABLE b;
CREATE TABLE a AS SELECT * FROM hr.employees;
CREATE TABLE b AS SELECT * FROM  hr.employees;
–反复插入,构造20万行+
INSERT INTO a SELECT * FROM a;
INSERT INTO b SELECT * FROM a;
COMMIT;
BEGIN
dbms_stats.gather_table_stats(ownname => USER,tabname => ‘a’,estimate_percent => 100,cascade => TRUE);
dbms_stats.gather_table_stats(ownname => USER,tabname => ‘b’,estimate_percent => 100,cascade => TRUE);
END;
/

1.测试IN,EXISTS在简单查询中,是等价的

SQL> set autotrace traceonly exp
SQL> SELECT * FROM a
2  WHERE EXISTS(
3  SELECT 1 FROM b WHERE a.employee_id=b.employee_id);

执行计划
———————————————————-
Plan hash value: 2317816356

———————————————————————————
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————
|   0 | SELECT STATEMENT       |        |   217K|    15M|  1375   (2)| 00:00:17 |
|*  1 |  HASH JOIN             |        |   217K|    15M|  1375   (2)| 00:00:17 |
|   2 |   SORT UNIQUE          |        |   217K|   848K|   126   (1)| 00:00:02 |
|   3 |    INDEX FAST FULL SCAN| IDX2_B |   217K|   848K|   126   (1)| 00:00:02 |
|   4 |   TABLE ACCESS FULL    | A      |   217K|    14M|   620   (1)| 00:00:08 |
———————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”EMPLOYEE_ID”=”B”.”EMPLOYEE_ID”)

SQL> SELECT * FROM a
2  WHERE a.employee_id IN (SELECT b.employee_id FROM b);

执行计划
———————————————————-
Plan hash value: 2317816356

———————————————————————————
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————
|   0 | SELECT STATEMENT       |        |   217K|    15M|  1375   (2)| 00:00:17 |
|*  1 |  HASH JOIN             |        |   217K|    15M|  1375   (2)| 00:00:17 |
|   2 |   SORT UNIQUE          |        |   217K|   848K|   126   (1)| 00:00:02 |
|   3 |    INDEX FAST FULL SCAN| IDX2_B |   217K|   848K|   126   (1)| 00:00:02 |
|   4 |   TABLE ACCESS FULL    | A      |   217K|    14M|   620   (1)| 00:00:08 |
———————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”EMPLOYEE_ID”=”B”.”EMPLOYEE_ID”)

可以看出,两个计划完全没有区别。类似于ORACLE查询重写为:
SELECT a.* FROM a,(SELECT DISTINCT b.employee_id FROM b) b1 WHERE a.employee_id=b1.employee_id;

看看8i时代的区别:

SQL> SELECT/*+optimizer_features_enable(‘8.1.7’)*/ * FROM a
2  WHERE EXISTS(
3  SELECT 1 FROM b WHERE a.employee_id=b.employee_id);
已用时间:  00: 00: 00.00

执行计划
———————————————————-
Plan hash value: 3422092984

————————————————————-
| Id  | Operation          | Name   | Rows  | Bytes | Cost  |
————————————————————-
|   0 | SELECT STATEMENT   |        | 10854 |   731K|   344 |
|*  1 |  FILTER            |        |       |       |       |
|   2 |   TABLE ACCESS FULL| A      | 10854 |   731K|   344 |
|*  3 |   INDEX RANGE SCAN | IDX2_B |  2049 |  8196 |     5 |
————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter( EXISTS (SELECT 0 FROM “B” “B” WHERE
“B”.”EMPLOYEE_ID”=:B1))
3 – access(“B”.”EMPLOYEE_ID”=:B1)

Note
—–
– cpu costing is off (consider enabling it)

SQL> SELECT/*+optimizer_features_enable(‘8.1.7’)*/ * FROM a
2  WHERE a.employee_id IN (SELECT b.employee_id FROM b);
已用时间:  00: 00: 00.00

执行计划
———————————————————-
Plan hash value: 1679318093

————————————————————————-
| Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost  |
————————————————————————-
|   0 | SELECT STATEMENT     |          |   217K|    16M|       |  1126 |
|*  1 |  HASH JOIN           |          |   217K|    16M|       |  1126 |
|   2 |   VIEW               | VW_NSO_1 |   106 |  1378 |       |   779 |
|   3 |    SORT UNIQUE       |          |   106 |   424 |  2576K|   779 |
|   4 |     TABLE ACCESS FULL| B        |   217K|   848K|       |   344 |
|   5 |   TABLE ACCESS FULL  | A        |   217K|    14M|       |   344 |
————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”EMPLOYEE_ID”=”EMPLOYEE_ID”)

Note
—–
– cpu costing is off (consider enabling it)

    显然,在8i时代,还是有明显的区别,EXISTS是主表驱动,走的FILTER,如果主表返回行很多,那么必然效率会低,如果主表小,内表,内表能走索引,是很好的,IN走HASH JOIN,受内表
驱动,内表返回行少,效率高。当然具体情况肯定会很复杂,这里不做研究了,因为8i已经是过去式了。

2.遇到优化器限制的做法:改写SQL
ORACLE优化器虽然已经很强大,但是还有很多限制,比如无法UNNEST的限制如子查询有CONNECT BY,SET操作,ROWNUM,关联子查询内部包含分组函数等。还比如SEMI JOIN.ANTI JOIN条件带OR的形式等。这时候,我们常用的优化方式就是SQL的等价改写,这要根据具体的业务和数据特点,来重写等价的SQL,千万别改写成结果不等价,那就糟糕了。

比如这个SQL:

SELECT * FROM a
WHERE EXISTS(
SELECT 1 FROM b WHERE a.department_id=b.department_id  GROUP BY b.department_id HAVING a.salary>=MAX(b.salary));

SQL> @display_cursor
SQL_ID  dgc8b80sxwct2, child number 1
————————————-
SELECT * FROM a WHERE EXISTS( SELECT 1 FROM b WHERE
a.department_id=b.department_id  GROUP BY b.department_id HAVING
a.salary>=MAX(b.salary))

Plan hash value: 705769378

—————————————————————————————–
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
—————————————————————————————–
|   0 | SELECT STATEMENT       |      |      1 |        |  22528 |00:09:13.61 |      32M|
|*  1 |  FILTER                |      |      1 |        |  22528 |00:09:13.61 |      32M|
|   2 |   TABLE ACCESS FULL    | A    |      1 |    217K|    217K|00:00:00.20 |    3733 |
|*  3 |   FILTER               |      |  14403 |        |   2058 |00:09:13.20 |      32M|
|   4 |    SORT GROUP BY NOSORT|      |  14403 |      1 |  14403 |00:09:13.17 |      32M|
|*  5 |     TABLE ACCESS FULL  | B    |  14403 |  19745 |    973M|00:06:17.21 |      32M|
—————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter( IS NOT NULL)
3 – filter(MAX(“B”.”SALARY”)<=:B1)
5 – filter(“B”.”DEPARTMENT_ID”=:B1)

这是个很简单的SQL,但是因为使用了EXISTS关联子查询,并且内部有分组操作,无法进行有效的查询转换,走了FILTER操作,FILTER操作类似于NESTED LOOPS,但是不同于NESTED LOOPS的是,他还可以通过条件判断,是否走子步骤。这里全表扫描B 14403次(这是无法忍受的,特别遇到大表,甭想跑出来了)

那么如何优化这种SQL呢,要改写,改写为JOIN形式:

 SQL> SELECT * FROM a,(SELECT department_id,MAX(b.salary) max_salary FROM b GROUP BY b.department_id) b1
2  WHERE a.department_id=b1.department_id AND a.salary>=b1.max_salary;

已选择22528行。

已用时间:  00: 00: 00.64

执行计划
———————————————————-
Plan hash value: 774961296

—————————————————————————–
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT     |      | 10854 |  1006K|  1250   (2)| 00:00:16 |
|*  1 |  HASH JOIN           |      | 10854 |  1006K|  1250   (2)| 00:00:16 |
|   2 |   VIEW               |      |    11 |   286 |   629   (3)| 00:00:08 |
|   3 |    HASH GROUP BY     |      |    11 |    88 |   629   (3)| 00:00:08 |
|   4 |     TABLE ACCESS FULL| B    |   217K|  1696K|   620   (1)| 00:00:08 |
|   5 |   TABLE ACCESS FULL  | A    |   217K|    14M|   620   (1)| 00:00:08 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”DEPARTMENT_ID”=”B1″.”DEPARTMENT_ID”)
filter(“A”.”SALARY”>=”B1″.”MAX_SALARY”)

统计信息
———————————————————-
0  recursive calls
0  db block gets
5986  consistent gets
0  physical reads
0  redo size
1851483  bytes sent via SQL*Net to client
16926  bytes received via SQL*Net from client
1503  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
22528  rows processed

从上面看出,逻辑读相对于没有改写之前的32M,时间9分钟,效率现在大幅度提升,时间变为00: 00: 00.64,l逻辑读变为5986次,因为走了HASH JOIN,相当于两表各扫描1次。

上面是通过将子查询改为JOIN的形式来优化,当然改写方式多样,下面用一个EXISTS改写为IN的方式来提高效率:

 SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE  a.department_id=b.department_id AND a.employee_id=b.employee_id
START WITH b.employee_id =202 CONNECT BY PRIOR b.employee_id=b.manager_id);

SQL> set autotrace traceonly exp
SQL>  SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE  a.department_id=b.department_id AND a.employee_id=b.employee_id
2    START WITH b.employee_id =202 CONNECT BY PRIOR b.employee_id=b.manager_id);
已用时间:  00: 00: 00.00

执行计划
———————————————————-
Plan hash value: 985844456

—————————————————————————————————
| Id  | Operation                                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————————
|   0 | SELECT STATEMENT                           |      |   186 | 12834 |   723K  (1)| 02:24:37 |
|*  1 |  FILTER                                    |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL                        | A    |   217K|    14M|   620   (1)| 00:00:08 |
|*  3 |   FILTER                                   |      |       |       |            |          |
|*  4 |    CONNECT BY NO FILTERING WITH SW (UNIQUE)|      |       |       |            |          |
|   5 |     TABLE ACCESS FULL                      | B    |   217K|  2545K|   620   (1)| 00:00:08 |
—————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter( EXISTS (SELECT 0 FROM “B” “B” WHERE “B”.”DEPARTMENT_ID”=:B1 AND
“B”.”EMPLOYEE_ID”=:B2 START WITH “B”.”EMPLOYEE_ID”=202 CONNECT BY “B”.”MANAGER_ID”=PRIOR
“B”.”EMPLOYEE_ID”))
3 – filter(“B”.”DEPARTMENT_ID”=:B1 AND “B”.”EMPLOYEE_ID”=:B2)
4 – access(“B”.”MANAGER_ID”=PRIOR “B”.”EMPLOYEE_ID”)
filter(“B”.”EMPLOYEE_ID”=202)

又是和前面类似的FILTER操作,这条SQL也要运行很长时间,FILTER操作不是不好,就像NESTED LOOPS一样,也有高效的时候,如果FILTER操作做的次数不多,而且分支操作可以高效实用索引,那么也是高效的,这得注意。

改写,以上查询可以很容易改写为IN的形式:

 SQL> SELECT * FROM a WHERE (a.department_id,a.employee_id) IN
2   (SELECT b.department_id,b.employee_id FROM b  START WITH b.employee_id =202 CONNECT BY PRIOR b.employee_id=b.manager_id );
已用时间:  00: 00: 00.13

执行计划
———————————————————-
Plan hash value: 1584203533

————————————————————————————————————–
| Id  | Operation                                 | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
————————————————————————————————————–
|   0 | SELECT STATEMENT                          |          |  2048 |   190K|       |  2466   (1)| 00:00:30 |
|*  1 |  HASH JOIN RIGHT SEMI                     |          |  2048 |   190K|  8064K|  2466   (1)| 00:00:30 |
|   2 |   VIEW                                    | VW_NSO_1 |   217K|  5514K|       |  1617  (62)| 00:00:20 |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|          |       |       |       |            |       |
|   4 |     TABLE ACCESS FULL                     | B        |   217K|  2545K|       |   620   (1)| 00:00:08 |
|   5 |   TABLE ACCESS FULL                       | A        |   217K|    14M|       |   620   (1)| 00:00:08 |
————————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”DEPARTMENT_ID”=”DEPARTMENT_ID” AND “A”.”EMPLOYEE_ID”=”EMPLOYEE_ID”)
3 – access(“B”.”MANAGER_ID”=PRIOR “B”.”EMPLOYEE_ID”)
filter(“B”.”EMPLOYEE_ID”=202)

走了HASH JOIN,这条SQL效率大增,基本在<1s内会返回结果。

在学习过程中,一定要亲自实践,不能遵循于从某个地方看到的规则,特别是N年前的规则,规则是有用的,但是规则也会不断滴更新的,如果规则发生了更新,但是在你的脑子里没有更新,你却不经过实践,就永远遵循这规则,那你对这方面的知识永远知之甚少或知而不全。

转载请注明:HugNew » IN/EXISTS子查询的区别及其优化方式

喜欢 (1)or分享 (0)
发表我的评论
取消评论

表情