----UNION 是需要排序的
drop table t1 purge;
create table t1 as select * from dba_objects where object_id is not null;
alter table t1 modify OBJECT_ID not null;
drop table t2 purge;
create table t2 as select * from dba_objects where object_id is not null;
alter table t2 modify OBJECT_ID not null;
set linesize 1000
set autotrace traceonly


select object_id from t1
union 
select object_id from t2;
执行计划
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   136K|  1732K|       |  1241  (55)| 00:00:15 |
|   1 |  SORT UNIQUE        |      |   136K|  1732K|  2705K|  1241  (55)| 00:00:15 |
|   2 |   UNION-ALL         |      |       |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T1   | 57994 |   736K|       |   292   (1)| 00:00:04 |
|   4 |    TABLE ACCESS FULL| T2   | 78456 |   996K|       |   292   (1)| 00:00:04 |
------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2094  consistent gets
          0  physical reads
          0  redo size
    1062305  bytes sent via SQL*Net to client
      54029  bytes received via SQL*Net from client
       4876  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      73120  rows processed
      
      
      
--发现索引无法消除UNION 排序（INDEX FAST FULL SCAN）
create index idx_t1_object_id on t1(object_id);
create index idx_t2_object_id on t2(object_id);
set autotrace traceonly
set linesize 1000


select  object_id from t1
union
select  object_id from t2;
执行计划
---------------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |   136K|  1732K|       |   755  (57)| 00:00:10 |
|   1 |  SORT UNIQUE           |                  |   136K|  1732K|  2705K|   755  (57)| 00:00:10 |
|   2 |   UNION-ALL            |                  |       |       |       |            |          |
|   3 |    INDEX FAST FULL SCAN| IDX_T1_OBJECT_ID | 57994 |   736K|       |    49   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| IDX_T2_OBJECT_ID | 78456 |   996K|       |    49   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        340  consistent gets
          0  physical reads
          0  redo size
    1062305  bytes sent via SQL*Net to client
      54029  bytes received via SQL*Net from client
       4876  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      73120  rows processed
      
--INDEX FULL SCAN的索引依然无法消除UNION排序
select /*+index(t1)*/ object_id from t1
union
select /*+index(t2)*/  object_id from t2; 
执行计划
----------------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   136K|  1732K|       |  1010  (56)| 00:00:13 |
|   1 |  SORT UNIQUE      |                  |   136K|  1732K|  2705K|  1010  (56)| 00:00:13 |
|   2 |   UNION-ALL       |                  |       |       |       |            |          |
|   3 |    INDEX FULL SCAN| IDX_T1_OBJECT_ID | 57994 |   736K|       |   177   (1)| 00:00:03 |
|   4 |    INDEX FULL SCAN| IDX_T2_OBJECT_ID | 78456 |   996K|       |   177   (1)| 00:00:03 |
----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        326  consistent gets
          0  physical reads
          0  redo size
    1062305  bytes sent via SQL*Net to client
      54029  bytes received via SQL*Net from client
       4876  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      73120  rows processed
      


--结论：索引无法消除UNION 排序，一般来说在使用UNION时要确定必要性，在数据不会重复时只需UNION ALL即可。