An exception occurred in the SQL Execution Plan after flashing back to drop to restore the table.
----- Normal execution plan
Set autotrace traceonly
Set linesize 1000
Select/* + index (t idx_object_id) */* from t where object_id = 19;
Execution Plan
----------------------------------------------------------
Plan hash value: 2041828949
Bytes ---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ---------------------------------------------------------------------------------------------
| 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 |
Bytes ---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID" = 19)
---- Immediately flashback drop after table t is accidentally dropped;
However, the corresponding index has been rename, But oracle can still use the index after this rename.
SQL> drop table t;
SQL> flashback table t to before drop;
----- Abnormal execution plan
SQL> select * from t where object_id = 19;
Execution Plan
----------------------------------------------------------
Plan hash value: 329240726
Bytes --------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------------------------
| 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 | ------ it seems that the performance has no major impact
Bytes --------------------------------------------------------------------------------------------------------------
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
------ Rename an index
Alter index "BIN $ GVgNy7hUF5HgUFAK8RIOcA = $0" rename to IDX_OBJECT_ID;