UNION  UNION ALL  效率
今天在测试一个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 ;
sql中union多表合并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 |
-------------------------------------------------------------------------------------------

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。