optimizer uses objects in Recyclebin or not!- Bug?

From: Vishnu Potukanuma <vishnupotukanuma_at_gmail.com>
Date: Sun, 24 Nov 2019 18:26:19 +0530
Message-ID: <CAP-RywyTPVqoL9rY5joAobX6aJaJGWONQfcQvcrwnKfc+UnjyQ_at_mail.gmail.com>

Can across an interesting scenario today and the case is as follows: it is reproducible.

create table temp (roll number, name varchar2(20), mark1 number, mark2 number, mark3 number);
alter table temp add constraint temp_pk primary key (roll); insert into temp select rownum,'asdf',1,1,1) from dual connect by level < 10000;
commit;
populating the table with entries.. .

drop table temp;
flashback table temp to before drop;

This is where things get crazy. Can optimizer make use of access paths related to the objects in recyclebin? when the query references a table that is not dropped...

---

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

---

| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP2 | 1 | 46 | 2 (0)|
00:00:01 |
|* 2 | INDEX UNIQUE SCAN | BIN$mBfmHJAdEPvgUw8AAAqw7g==$0 | 1 |
| 1 (0)| 00:00:01 |

---

Oracle 19c documentation suggests that * Oracle Database retrieves all indexes defined on the table except for bitmap join indexes, and all triggers and constraints defined on the table except for referential integrity constraints that reference other tables.*

in a sense it is retriving all the indexes defined on the table, but the naming format appears to be BIN$ which is the case for the objects in recyclebin...

things get even weird here... object name appears to be in BIN... SQL> purge index "BIN$mBfmHJAdEPvgUw8AAAqw7g==$0"; purge index "BIN$mBfmHJAdEPvgUw8AAAqw7g==$0" *
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN

So the object is not in recyclebin but the naming doesn't make sense.... 1. flashback index doesn't make sense as the syntax is not valid 2. Index is already restored.

the only way to restore to its previous name is to rename it.

SQL> alter index "BIN$mBfmHJAdEPvgUw8AAAqw7g==$0" rename to temp_pk;

Index altered.

Can someone please tell me whether i missed something something here or Is it a bug?

Thanks,
Vishnu

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Nov 24 2019 - 13:56:19 CET