ACCESS闪转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;

相关文章