【转】UNION效率比UNION ALL效率高——SQL优化之Everything is possible

今天在测试一个SQL的时候发现，居然使用UNION要比使用UNION ALL的效率高。

具体SQL语句如下：

SQL> select count(*)
2 from
3 (
4 select a.id, c.plat_name, a.plat_id, a.substitute_flag
5 from cat_auth_price a, plt_plat c
6 where a.id in
7 (
8 (
9 select d12.price_id
10 from cat_auth_price_drug12 d12,cat_drug d
11 where d12.drug_id=d.id and d.name_chn like '%'
12 )
13 union all
14 (
15 select d20.price_id from cat_auth_price_drug20 d20 ,cat_drug d
16 where d20.drug_id=d.id and d.name_chn like '%'
17 )
18 )
19 and a.plat_id = c.id
20 and (a.plat_id = 'FR20T0000020000000000032' or a.plat_id = 'FR20T0000020000000000001')
21 )
22 ;

COUNT(*)
----------
6437

Elapsed: 00:00:00.81
SQL> select count(*)
2 from
3 (
4 select a.id, c.plat_name, a.plat_id, a.substitute_flag
5 from cat_auth_price a, plt_plat c
6 where a.id in
7 (
8 (
9 select d12.price_id
10 from cat_auth_price_drug12 d12,cat_drug d
11 where d12.drug_id=d.id and d.name_chn like '%'
12 )
13 union
14 (
15 select d20.price_id from cat_auth_price_drug20 d20 ,cat_drug d
16 where d20.drug_id=d.id and d.name_chn like '%'
17 )
18 )
19 and a.plat_id = c.id
20 and (a.plat_id = 'FR20T0000020000000000032' or a.plat_id = 'FR20T0000020000000000001')
21 )
22 ;

COUNT(*)
----------
6437

Elapsed: 00:00:00.71

对比两个SQL语句，唯一的区别就是一个是UNION ALL另一个是UNION，而且按照一般的规律，UNION ALL的速度会更快一些，因为不需要进行排序去重的操作。

考虑到集合操作是在IN语句中，猜测UNION速度快是由于去掉了重复值，使得IN的结果集变小，导致速度变快。结果看了一下执行计划，发现和我想的完全不一样：

SQL> explain plan for
2 select count(*)
3 from
4 (
5 select a.id, c.plat_name, a.plat_id, a.substitute_flag
6 from cat_auth_price a, plt_plat c
7 where a.id in
8 (
9 (
10 select d12.price_id
11 from cat_auth_price_drug12 d12,cat_drug d
12 where d12.drug_id=d.id and d.name_chn like '%'
13 )
14 union
15 (
16 select d20.price_id from cat_auth_price_drug20 d20 ,cat_drug d
17 where d20.drug_id=d.id and d.name_chn like '%'
18 )
19 )
20 and a.plat_id = c.id
21 and (a.plat_id = 'FR20T0000020000000000032' or a.plat_id = 'FR20T0000020000000000001')
22 )
23 ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | | 3862 |
| 1 | SORT AGGREGATE | | 1 | 101 | | |
| 2 | HASH JOIN | | 548 | 55348 | | 3862 |
| 3 | TABLE ACCESS BY INDEX ROWID| CAT_AUTH_PRICE | 220 | 11000 | | 70 |
| 4 | NESTED LOOPS | | 440 | 33000 | | 142 |
| 5 | INLIST ITERATOR | | | | | |
| 6 | INDEX RANGE SCAN | PK_PLT_PLAT | 2 | 50 | | 2 |
| 7 | INLIST ITERATOR | | | | | |
| 8 | INDEX RANGE SCAN | TU_CAT_AUTH_PRICE_PLATID| 220 | | | 38 |
| 9 | VIEW | VW_NSO_1 | 149K| 3785K| | 3716 |
| 10 | SORT UNIQUE | | 149K| 12M| 28M| 3716 |
| 11 | UNION-ALL | | | | | |
| 12 | HASH JOIN | | 24662 | 2191K| 1496K| 708 |
| 13 | TABLE ACCESS FULL | CAT_AUTH_PRICE_DRUG12 | 24662 | 1204K| | 20 |
| 14 | TABLE ACCESS FULL | CAT_DRUG | 93917 | 3760K| | 588 |
| 15 | HASH JOIN | | 124K| 10M| 4864K| 873 |
| 16 | TABLE ACCESS FULL | CAT_DRUG | 93917 | 3760K| | 588 |
| 17 | TABLE ACCESS FULL | CAT_AUTH_PRICE_DRUG20 | 124K| 6076K| | 92 |
-------------------------------------------------------------------------------------------

25 rows selected.

SQL> explain plan for
2 select count(*)
3 from
4 (
5 select a.id, c.plat_name, a.plat_id, a.substitute_flag
6 from cat_auth_price a, plt_plat c
7 where a.id in
8 (
9 (
10 select d12.price_id
11 from cat_auth_price_drug12 d12,cat_drug d
12 where d12.drug_id=d.id and d.name_chn like '%'
13 )
14 union all
15 (
16 select d20.price_id from cat_auth_price_drug20 d20 ,cat_drug d
17 where d20.drug_id=d.id and d.name_chn like '%'
18 )
19 )
20 and a.plat_id = c.id
21 and (a.plat_id = 'FR20T0000020000000000032' or a.plat_id = 'FR20T0000020000000000001')
22 )
23 ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | | 679 |
| 1 | SORT AGGREGATE | | 1 | 100 | | |
| 2 | NESTED LOOPS | | 548 | 54800 | | 679 |
| 3 | HASH JOIN | | 9037 | 661K| | 679 |
| 4 | TABLE ACCESS FULL | CAT_AUTH_PRICE | 7256 | 354K| | 528 |
| 5 | VIEW | VW_NSO_1 | 149K| 3640K| | 110 |
| 6 | SORT UNIQUE | | | | | |
| 7 | UNION-ALL | | | | | |
| 8 | HASH JOIN | | 24662 | 2191K| 1496K| 708 |
| 9 | TABLE ACCESS FULL| CAT_AUTH_PRICE_DRUG12 | 24662 | 1204K| | 20 |
| 10 | TABLE ACCESS FULL| CAT_DRUG | 93917 | 3760K| | 588 |
| 11 | HASH JOIN | | 124K| 10M| 4864K| 873 |
| 12 | TABLE ACCESS FULL| CAT_DRUG | 93917 | 3760K| | 588 |
| 13 | TABLE ACCESS FULL| CAT_AUTH_PRICE_DRUG20 | 124K| 6076K| | 92 |
| 14 | INLIST ITERATOR | | | | | |
| 15 | INDEX UNIQUE SCAN | PK_PLT_PLAT | 1 | 25 | | |
-------------------------------------------------------------------------------------------

23 rows selected.

对比两个SQL的执行计划，发现差异很大，表的连接方式、连接顺序和访问方式都有很大的差别。

到是我猜测的UNION ALL和UNION由于有IN的存在而没有任何的区别。

有的时候UNION和UNION ALL得到的结果一样，但是可能会改变整个查询的执行计划，这一点需要小心。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26892340/viewspace-721496/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26892340/viewspace-721496/