閃回drop恢複表後sql執行計畫異常，drop執行計畫

閃回drop恢複表後sql執行計畫異常，drop執行計畫


-----正常執行計畫
set autotrace traceonly
set linesize 1000

select /*+index(t idx_object_id)*/ * from t where object_id=19;
Execution Plan
----------------------------------------------------------
Plan hash value: 2041828949
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                               |     1 |         207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1     |   207 |           2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN               | IDX_OBJECT_ID |     1 |                |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=19)


----在誤操作drop 表t 後，立即flashback drop；
但是之前相應的索引已經被rename了，但是oracle依然可以這個這個rename後的索引

SQL> drop table t;
SQL> flashback table t to before drop;


-----異常執行計畫
SQL> select   * from t where object_id=19;
Execution Plan
----------------------------------------------------------
Plan hash value: 329240726
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                              |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN            |BIN$GVgNy7hUF5HgUFAK8RIOcA==$0 |     1 |            |     1   (0)| 00:00:01 |------貌似效能沒有大影響
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=19)


SQL> select index_name,status from user_indexes where table_name='T';
BIN$GVgNy7hUF5HgUFAK8RIOcA==$0         VALID  

------重新命名索引

alter index "BIN$GVgNy7hUF5HgUFAK8RIOcA==$0" rename to IDX_OBJECT_ID;