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

Now that you mention it houri, this raises a different issue probably unless we test.. we are not 100% sure whether the statisitics history and/or synoposis collected will be dropped as well... as per the documentation,.

When you drop a table, workload information used by the auto-histogram gathering feature and saved statistics history used by the RESTORE_*_STATS procedures
is lost. Without this data, these features do not function properly. To remove all rows from a table, and to restore these statistics with DBMS_STATS, use TRUNCATE instead of dropping and re-creating the same table.

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