Oracle的flashback drop與索引

今天在跟朋友討論了如下話題：

《有一張表上建立索引，DROP該表，又閃回刪除回來，請問，這張表上的索引還能用嗎？如果有變化那是什嗎？》

我進行了如下測試：

sys@MAA> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

-- 我的TEST2表上有兩個索引

luocs@MAA> select index_name, status from user_indexes where table_name='TEST2';

INDEX_NAME                                                   STATUS

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

INX_TEST2_ZH                                                 VALID

INX_TEST2                                                    VALID

luocs@MAA> set autot trace exp stat

luocs@MAA> select count(*) from test2 where id=1;

Execution Plan

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

Plan hash value: 3565898414

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

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |              |     1 |     5 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |              |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| INX_TEST2_ZH |     2 |    10 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=1)

Statistics

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

1  recursive calls

0  db block gets

2  consistent gets

0  physical reads

0  redo size

  525  bytes sent via SQL*Net to client

  523  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

-- DROP 表TEST2

luocs@MAA> drop table test2;

Table dropped.

-- 索引也被刪除

luocs@MAA> select index_name, status from user_indexes where table_name='TEST2';

no rows selected

-- 這時候查看資源回收筒，我們看到表的刪除記錄，卻沒有看到索引的

luocs@MAA> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

TEST2            BIN$048hXdZTf0HgQwEAAH/4UQ==$0 TABLE        2013-01-18:19:20:06

-- 進行閃回刪除

luocs@MAA> flashback table test2 to before drop;

Flashback complete.

-- 這時候又能看到索引資訊，也是可用狀態，卻索引名稱改變，依然使用資源回收筒裡的名字

luocs@MAA> select index_name, status from user_indexes where table_name='TEST2';

INDEX_NAME                                                   STATUS

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

BIN$048hXdZSf0HgQwEAAH/4UQ==$0                               VALID

BIN$048hXdZRf0HgQwEAAH/4UQ==$0                               VALID

-- 我們也看到該索引能夠正常使用

luocs@MAA> set autot trace exp

luocs@MAA> select count(*) from test2 where id=1;

Execution Plan

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

Plan hash value: 3980542661

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

| Id  | Operation         | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |                                |     1 |     5 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                                |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| BIN$048hXdZSf0HgQwEAAH/4UQ==$0 |     2 |    10 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=1)