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