BIN$ Index Found in the Execution Plan – Digging through the Recycle Bin

28 11 2011

November 28, 2011

There are a few articles that can be found on the Internet that describe the cause of indexes with names similar to BIN$ld5VAtb88PngQAB/AQF8hg==$0 and BIN$PESygWW5R0WhbOaDugxqwQ==$0 appearing in execution plans.  As is likely known by readers, the Oracle Database documentation describes that these object names are associated with the recycle bin that was introduced in Oracle Database 10.1.  When an object is dropped (but not purged), it is placed into the recycle bin with a name that begins with BIN$ and ends with ==$ followed by a number (the version, which in brief testing seems to always be 0).

I have answered this question a couple of times in the past in various Oracle Database forums, including a recent OTN thread.  What is the significance of having an index named, for instance, BIN$ld5VAtb88PngQAB/AQF8hg==$0 in an execution plan.  Does that mean that Oracle’s query optimizer has selected to use a dropped index?  No.  The simple answer is that the table to which the index belongs was dropped and then flashed back to before the drop.  When this happens, the table name is restored to its original name, but the names of the associated indexes are not restored.

A quick test case to demonstrate.  First, we will create a table with an index, and then collect statistics:

DROP TABLE T1 PURGE;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  RPAD('A',255,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

CREATE INDEX IND_T1_C1 ON T1(C1);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE) 

Let’s put together a simple SQL statement that will hopefully use the index, and then confirm that the index was in fact used by displaying the actual execution plan used for the SQL statement:

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  g0kkvxqg3v145, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5

Plan hash value: 683303157

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     5 |  1300 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C1 |     5 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5) 

The above plan shows that the IND_T1_C1 index was used for this particular SQL statement.

Next, we will purge the recycle bin (make certain that there is nothing useful in the recycle bin first), drop the index, and see if it can still be used in an execution plan:

DROP INDEX IND_T1_C1;

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  g0kkvxqg3v145, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   137 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     5 |  1300 |   137   (5)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1"<=5)

/* SHOW RECYCLEBIN  should be roughly equivalent to the following SQL statement */
SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME;

no rows selected 

From the above, it is apparent that an index that is dropped will not appear in an execution plan.  The dropped index is not in the recycle bin either.

Let’s recreate the index:

CREATE INDEX IND_T1_C1 ON T1(C1);

Then, using SQL statements similar to those at the start of this article (without the PURGE clause in the DROP TABLE statement), we will drop and recreate the table:

DROP TABLE T1;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  RPAD('A',255,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

CREATE INDEX IND_T1_C1 ON T1(C1);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE) 

Next, let’s take a look at the recycle bin (note that we could simply execute SHOW RECYCLEBIN rather than execute the SQL statement, but the index would not be listed using that method):

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME;

OBJECT_NAME                    ORIGINAL_N TYPE  CREATETIME
------------------------------ ---------- ----- -------------------
BIN$cU4bWUSaSu2PUYdJvOq+hA==$0 IND_T1_C1  INDEX 2011-11-28:07:21:30
BIN$2smXLnTGTSqcBa8SJucvtg==$0 T1         TABLE 2011-11-28:07:21:30 

The above shows that we now have one table and its index in the recycle bin.  Let’s repeat the drop and recreate:

DROP TABLE T1;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  RPAD('A',255,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

CREATE INDEX IND_T1_C1 ON T1(C1);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE) 

Now let’s take a look at the recycle bin’s contents again:

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME;

OBJECT_NAME                    ORIGINAL_N TYPE  CREATETIME
------------------------------ ---------- ----- -------------------
BIN$cU4bWUSaSu2PUYdJvOq+hA==$0 IND_T1_C1  INDEX 2011-11-28:07:21:30
BIN$/40oC3RJSNiLmEESZ7VNEw==$0 IND_T1_C1  INDEX 2011-11-28:07:21:48
BIN$2smXLnTGTSqcBa8SJucvtg==$0 T1         TABLE 2011-11-28:07:21:30
BIN$nYId4wdGRf6IgpSXSDb4Kw==$0 T1         TABLE 2011-11-28:07:21:48 

The above output now shows that there are two tables and their associated indexes in the recycle bin.  Let’s recover one of those tables and its index:

FLASHBACK TABLE T1 TO BEFORE DROP;

Flashback complete. 

A quick check of the recycle bin shows that the most recently dropped table and its associated index are no longer in the recycle bin, but the older version of table T1 and its index are still in the recycle bin:

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME; 

OBJECT_NAME                    ORIGINAL_N TYPE  CREATETIME
------------------------------ ---------- ----- -------------------
BIN$cU4bWUSaSu2PUYdJvOq+hA==$0 IND_T1_C1  INDEX 2011-11-28:07:21:30
BIN$2smXLnTGTSqcBa8SJucvtg==$0 T1         TABLE 2011-11-28:07:21:30

Let’s re-execute the SQL statement that queries table T1:

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA 

So, after recoving the table from the recycle bin, we are able to query the table.  Let’s take a look at the execution plan for this query:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  g0kkvxqg3v145, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5

Plan hash value: 1395723482

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                                |       |       |     3 (100)|       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1                             |     5 |  1300 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BIN$/40oC3RJSNiLmEESZ7VNEw==$0 |     5 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5) 

Notice in the above execution plan, the index name of BIN$/40oC3RJSNiLmEESZ7VNEw==$0 – that is what the index was named when it was sent to the recycle bin.  Let’s fix the odd BIN$ name and re-execute the query:

ALTER INDEX "BIN$/40oC3RJSNiLmEESZ7VNEw==$0" RENAME TO IND_T1_C1;

Index altered. 

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  g0kkvxqg3v145, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1 WHERE   C1<=5

Plan hash value: 683303157

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     5 |  1300 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C1 |     5 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5)

So, the above shows how a BIN$ named index might appear in an execution plan, and how to fix the name.

We still have one table and its index in the recycle bin.  Let’s take a quick look at that table:

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  "BIN$2smXLnTGTSqcBa8SJucvtg==$0"
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA 

We are able to query the table that is in the recycle bin, as long as we enclose the table name (OBJECT_NAME in the query of RECYCLEBIN) in quotation marks ().  Let’s take a look at the execution plan for the previous SQL statement:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  a2a2vcsbtw5ac, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   "BIN$2smXLnTGTSqcBa8SJucvtg==$0"
WHERE   C1<=5

Plan hash value: 3681245720

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                                |       |       |     3 (100)|       |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIN$2smXLnTGTSqcBa8SJucvtg==$0 |     5 |  1300 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BIN$cU4bWUSaSu2PUYdJvOq+hA==$0 |     5 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5) 

Now we have both a BIN$ prefixed table and index in the execution plan.

Let’s recover the old version of the T1 table (renaming it when it is recovered) and rename its associated recovered index:

FLASHBACK TABLE T1 TO BEFORE DROP RENAME TO T1_OLD;

Flashback complete.

ALTER INDEX "BIN$cU4bWUSaSu2PUYdJvOq+hA==$0" RENAME TO IND_T1_OLD_C1;

Index altered. 

Let’s query the recovered table (now called T1_OLD) and check the execution plan:

SELECT
  C1,
  SUBSTR(C2,1,2)
FROM
  T1_OLD
WHERE
  C1<=5;

 C1 SU
--- --
  1 AA
  2 AA
  3 AA
  4 AA
  5 AA

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  bv1ty7jq2hc5g, child number 0
-------------------------------------
SELECT   C1,   SUBSTR(C2,1,2) FROM   T1_OLD WHERE   C1<=5

Plan hash value: 3358254750

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1_OLD        |     5 |  1300 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_OLD_C1 |     5 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=5) 

Now let’s make certain that there is nothing in the recycle bin:

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME;

no rows selected 

The experimental tables and indexes are not in the recycle bin.  Let’s drop the experimental tables that we recovered from the recycle bin, this time skipping the recycle bin:

DROP TABLE T1 PURGE;
DROP TABLE T1_OLD PURGE; 

Just to confirm that the tables and their associated indexes are not in the recycle bin:

SELECT
  OBJECT_NAME,
  ORIGINAL_NAME,
  TYPE,
  CREATETIME
FROM
  RECYCLEBIN
ORDER BY
  TYPE,
  ORIGINAL_NAME,
  OBJECT_NAME,
  CREATETIME;

no rows selected 

—–

Hopefully, by now everyone is aware of those BIN$ prefixed object names that might appear in execution plans, what causes the names to be created, and how to fix the names.


Actions

Information

4 responses

28 11 2011
Volim Zagreb

Nice!

28 11 2011
28 11 2011
Charles Hooper

Gary,

Thank you for linking to your article. I vaguely recall reading that article once before, and it seems to be directly related to this article. Does your blogging software provide monospaced (Courier) code sections? If so, that would make the test case that you included in the blog article a lot easier to read.

28 11 2011
Recycle bin « Timur Akhmadeev's blog

[...] Recent Charles Hooper’s post on the topic of Recycle bin (which is, BTW, documented behavior) reminded me of an issue with that functionality I’ve seen recently. The problem was a single-row INSERT INTO table VALUES () statement was hanging for more than an hour burning CPU. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 148 other followers

%d bloggers like this: