闪回drop恢复表后sql运行计划异常



-----正常运行计划
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;



posted @ 2017-07-16 13:57  llguanli  阅读(150)  评论(0编辑  收藏  举报