Home -> Mailing Lists -> Oracle-L -> Re: optimizer uses objects in Recyclebin or not!- Bug?

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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 24 Nov 2019 16:50:24 +0000
Message-ID: <CWXP265MB1750CB5A8D23E44872D38121A54B0_at_CWXP265MB1750.GBRP265.PROD.OUTLOOK.COM> 







Vishnu,



If you quote from the documentation it would be nice if you could supply a URL as well.  Searching for the text you supplied it seems to be the second sentence of the following paragraph:





If you need to remove all rows from a table when using DBMS_STATS, use TRUNCATE instead of dropping and re-creating the same table. 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.





The context is about truncating a table instead of dropping and recreating it. If you drop a table then execute a CREATE TABLE statement to create a table of the same name in the same schema then an attempt to "flashback to before drop" will result in Oracle error:



ERROR at line 1:


ORA-38312: original name is used by an existing object




Regards


Jonathan Lewis





From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Vishnu Potukanuma <vishnupotukanuma_at_gmail.com>
Sent: 24 November 2019 16:29


To: Mohamed Houri


Cc: Oracle L


Subject: 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<mailto: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


Received on Sun Nov 24 2019 - 17:50:24 CET












Original text of this message