Oracle's flashback Drop and Index

Today, I was discussing with my friends the following topics:

"There is a table on the index, drop the table, and flash back to delete back, excuse me, the index on this table can still use?" If there's a change, what is it? 》

I conducted the following tests:

Sys@maa> select * from V$version;

BANNER

----------------------------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition release 11.2.0.3.0-64bit Production

Pl/sql Release 11.2.0.3.0-production

CORE 11.2.0.3.0 Production

TNS for Linux:version 11.2.0.3.0-production

Nlsrtl Version 11.2.0.3.0-production

--I have two indexes on my TEST2 table.

Luocs@maa> Select Index_name, status from User_indexes where table_name= ' TEST2 ';

Index_name STATUS

------------------------------------------------------------ ----------------

Inx_test2_zh VALID

Inx_test2 VALID

luocs@maa> set Autot trace exp Stat

Luocs@maa> Select COUNT (*) from test2 where id=1;

Execution Plan

----------------------------------------------------------

Plan Hash value:3565898414

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

----------------------------------------------------------------------------------

| 0 |              SELECT STATEMENT |     |     1 |     5 | 2 (0) | 00:00:01 |

|  1 |              SORT AGGREGATE |     |     1 |            5 |          | |

|* 2 | INDEX RANGE scan|     Inx_test2_zh |    2 |     10 | 2 (0) | 00:00:01 |

----------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("ID" =1)

Statistics

----------------------------------------------------------

1 Recursive calls

0 db Block gets

2 consistent gets

0 physical Reads

0 Redo Size

525 Bytes sent via sql*net to client

523 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

1 rows processed

--DROP Table TEST2

luocs@maa> drop table test2;

Table dropped.

--The index is also deleted

Luocs@maa> Select Index_name, status from User_indexes where table_name= ' TEST2 ';

No rows selected

--This time we look at the Recycle Bin, we see the delete record of the table, but we don't see the index

Luocs@maa> Show RecycleBin

ORIGINAL name RecycleBin name OBJECT TYPE DROP Time

---------------- ------------------------------ ------------ -------------------

TEST2 bin$048hxdztf0hgqweaah/4uq==$0 TABLE 2013-01-18:19:20:06

--flash-back Delete

Luocs@maa> Flashback table Test2 to before drop;

Flashback complete.

-This time you can see the index information, but also the available state, but the index name changes, still use the name of the Recycle Bin

Luocs@maa> Select Index_name, status from User_indexes where table_name= ' TEST2 ';

Index_name STATUS

------------------------------------------------------------ ----------------

Bin$048hxdzsf0hgqweaah/4uq==$0 VALID

Bin$048hxdzrf0hgqweaah/4uq==$0 VALID

--We also see that the index can be used correctly

luocs@maa> Set Autot trace exp

Luocs@maa> Select COUNT (*) from test2 where id=1;

Execution Plan

----------------------------------------------------------

Plan Hash value:3980542661

----------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

----------------------------------------------------------------------------------------------------

| 0 |                                SELECT STATEMENT |     |     1 |     5 | 2 (0) | 00:00:01 |

|  1 |                                SORT AGGREGATE |     |     1 |            5 |          | |

|* 2 | INDEX RANGE scan|     bin$048hxdzsf0hgqweaah/4uq==$0 |    2 |     10 | 2 (0) | 00:00:01 |

----------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("ID" =1)