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