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.
Charles Hooper's Oracle Notes 
Nice!
Triggers do exactly the same. http://colbran.co.za/wordpress/2010/01/22/recycled-triggers/
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.
[…] 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. […]