Re: optimizer uses objects in Recyclebin or not!- Bug?

yes true, not sure how i missed this

From a developer perspective, it looks more like redesigning the existing architecture to preserve the names of triggers and constraints etc which can be more work integrating other modules, but wait, what is the best way to push the code without worrying about it, lets write half of it and document the rest of it!

   -

   The retrieved indexes, triggers, and constraints have recycle bin names.    Therefore it is advisable to query the USER_RECYCLEBIN view before    issuing a FLASHBACK TABLE ... TO BEFORE DROP statement so that you can    rename the retrieved triggers and constraints to more usable names

Thanks,
Vishnu

On Sun, Nov 24, 2019 at 7:49 PM Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

> It is not only the index name which is not flashed back but a couple of
> other table objects as I explained in this blog post
>
> https://hourim.wordpress.com/2012/11/14/recycle-bin-whats-going-on/
>
> And things become interesting in this context when you are using a SPM
> baseline. Dropping and flashing back a table can preempt the CBO from using
> that SPM plan if this one uses an index from that dropped & flashed back
> table
>
>
> https://hourim.wordpress.com/2014/01/24/sql-plan-management-and-table-flashback/
>
> *Bottom line*: when you drop and flashback a table, then think about the
> following points
>
> 1.
>
> the foreign key constraints are not flashed back
> 2.
>
> the original index name, the trigger name and constraint name are not flashed
> back <https://hourim.wordpress.com/?s=recycle>
> 3.
>
> any SQL plan baseline based on an index created on a table that has
> been dropped and flashed back will not be reproducible until you give that
> index its original name
>
> But I haven't tested this in recent releases.
>
> Best regards
>
> Mohamed Houri
>

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