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

28112011

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:

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:

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:

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:

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.

[…] 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. […]

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: