索引系列八--索引特性之有序难优化union
----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即可。