-----Normal Operation 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
---------------------------------------------------------------------------------------------
| 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)
----After the drop table T has been mistakenly manipulated. Flashback drop immediately;
However, the corresponding index has been rename. But Oracle is still able to get this rename index
sql> drop table t;
Sql> Flashback table T to before drop;
-----Exception Run Schedule
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 |------Looks like performance has no big impact
--------------------------------------------------------------------------------------------------------------
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
------Renaming an Index
Alter index "BIN$GVGNY7HUF5HGUFAK8RIOCA==$0" Rename to idx_object_id;
SQL Run Schedule exception after Flashback drop recovery table
