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

执行计划

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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;

执行计划

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 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;

执行计划

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 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即可。

本文题目：索引系列八--索引特性之有序难优化union
链接URL：http://azwzsj.com/article/jeedji.html

索引系列八--索引特性之有序难优化union

其他资讯