union 优化方法
union优化思路:
union=>remove duplicate=>sort=>index(don't remove sort)=>index fast full scan(read many index blocks once)
union总结:
返回值仅是索引列值的情况下,给
返回值列加索引是可以
优化union!
union and union all 简介
union:合并结果集并去除重复(remove duplicate)记录
union all:合并结果集并不去除重复记录
实验如下:
SQL> create table t_union as select * from dba_objects;
Table created.
SQL> alter table t_union modify object_id not null;
Table altered.
SQL> create table t_union_1 as select * from dba_objects;
Table created.
SQL> alter table t_union_1 modify object_id not null;
Table altered.
SQL> select object_id from t_union
2 union
3 select object_id from t_union_1;
81512 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 299238292
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 169K| 2153K| | 1460 (52)| 00:00:18 |
| 1 | SORT UNIQUE | | 169K| 2153K| 3352K| 1460 (52)| 00:00:18 |
| 2 | UNION-ALL | | | | | | |
| 3 | TABLE ACCESS FULL| T_UNION | 82154 | 1042K| | 325 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL| T_UNION_1 | 87476 | 1110K| | 325 (1)| 00:00:04 |
-----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2332 consistent gets
0 physical reads
0 redo size
1185377 bytes sent via SQL*Net to client
60189 bytes received via SQL*Net from client
5436 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
81512 rows processed
=>TempSpc和1 sorts (memory)证明了union是需要排序的;UNION-ALL代表了我们执行合并操作<=
SQL> select object_id from t_union
2 union all
3 select object_id from t_union_1;
163023 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3974255051
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 169K| 2153K| 650 (51)| 00:00:08 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL| T_UNION | 82154 | 1042K| 325 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| T_UNION_1 | 87476 | 1110K| 325 (1)| 00:00:04 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13034 consistent gets
0 physical reads
0 redo size
2370239 bytes sent via SQL*Net to client
119963 bytes received via SQL*Net from client
10870 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
163023 rows processed
=>sorts值为0,也证明了union all不需要排序的;UNION-ALL代表了我们执行合并操作<=
给返回值列,添加索引看union and union all是否得到优化
SQL> create index t1 on t_union(object_id);
Index created.
SQL> create index t2 on t_union_1(object_id);
Index created.
SQL> select object_id from t_union
2 union
3 select object_id from t_union_1;
81512 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3228413298
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 169K| 2153K| | 919 (52)| 00:00:12 |
| 1 | SORT UNIQUE | | 169K| 2153K| 3352K| 919 (52)| 00:00:12 |
| 2 | UNION-ALL | | | | | | |
| 3 | INDEX FAST FULL SCAN| T1 | 82154 | 1042K| | 54 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| T2 | 87476 | 1110K| | 54 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
378 consistent gets
0 physical reads
0 redo size
1185377 bytes sent via SQL*Net to client
60189 bytes received via SQL*Net from client
5436 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
81512 rows processed
1、索引优化了union ==>INDEX FAST FULL SCAN
2、索引不能消除union的排序 ==>1 sorts (memory) 、TempSpc
SQL> select object_id from t_union
2 union all
3 select object_id from t_union_1;
163023 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 937658497
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 169K| 2153K| 109 (51)| 00:00:02 |
| 1 | UNION-ALL | | | | | |
| 2 | INDEX FAST FULL SCAN| T1 | 82154 | 1042K| 54 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T2 | 87476 | 1110K| 54 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11221 consistent gets
0 physical reads
0 redo size
2370239 bytes sent via SQL*Net to client
119963 bytes received via SQL*Net from client
10870 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
163023 rows processed
1、索引不能很好的优化union all
参考:《收获,不止ORACLE》
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26442936/viewspace-775041/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-775041/