Oracle-L: Recompiling Invalid Objects after Table Rename

From: Bill Buchan <wbuchan_at_uk.intasys.com>
Date: Mon, 15 Apr 2002 04:23:28 -0800
Message-ID: <F001.00444449.20020415042328@fatcity.com>

Hi all,

We have an application which deletes a large number of rows from a table. It would be faster to simply insert the rows that we want to keep into a second table, drop the original table and then rename the second table to that of the one we have just dropped.

The only downside that I can see is that all the source objects which reference the original table become invalid.

We could:
1. Simply allow the source objects to be recompiled naturally overtime as they are reused (but with the possibility of a large number of invalid objects at any one time in the database and little control over when compilation is done).
2. Force recompilation following the drop table. However this would require logging all objects which would need recompilation. This is an additional step for any new development and would therefore the list of object would be prone to become inaccurate over time. (Could maybe do this automatically using USER_REFERENCES prior to the drop table? - still seems a bit clumsy)

Does anyone have any comments on doing this?

Many thanks
- Bill.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bill Buchan
INET: wbuchan_at_uk.intasys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Apr 15 2002 - 07:23:28 CDT