【轉】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得到的結果一樣，但是可能會改變整個查詢的執行計劃，這一點需要小心。