今天在跟朋友讨论了如下话题：
《有一张表上建立索引，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)