果然一个sort一个hash
SQL> create table o1 as select level l from dual connect by level<=1e4;
表已创建。
SQL> create table o2 as select level*2-1 l from dual connect by level<=1e4;
表已创建。
SQL> create table o3 as select level*2l from dual connect by level<=1e4;
表已创建。
SQL> set autot trace
SQL> select l from o1 union select l from o2 union select l from o3;
已选择20000行。
执行计划
----------------------------------------------------------
Plan hash value: 3960457518
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30000 | 380K| 22 (73)| 00:00:01 |
| 1 | SORT UNIQUE | | 30000 | 380K| 22 (73)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS FULL| O1 | 10000 | 126K| 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| O2 | 10000 | 126K| 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| O3 | 10000 | 126K| 6 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
19 recursive calls
0 db block gets
126 consistent gets
48 physical reads
0 redo size
358260 bytes sent via SQL*Net to client
15183 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20000 rows processed
SQL> select distinct l from(select l from o1 union all select l from o2 union all select l from o3);
已选择20000行。
执行计划
----------------------------------------------------------
Plan hash value: 515367191
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30000 | 380K| 20 (10)| 00:00:01 |
| 1 | HASH UNIQUE | | 30000 | 380K| 20 (10)| 00:00:01 |
| 2 | VIEW | | 30000 | 380K| 18 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS FULL| O1 | 10000 | 126K| 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| O2 | 10000 | 126K| 6 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| O3 | 10000 | 126K| 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
10 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
358260 bytes sent via SQL*Net to client
15183 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20000 rows processed