Re: Avoid the fragmentation in 9.2

"cschang" <cschang_at_maxinter.net> wrote in message news:1122ij2kudff2a4_at_corp.supernews.com...
> We have a database in 9.2.0.5. Recently, we set up a procedure that will
> load about 1.5 millions records into a tmp table, drop a old table rename
> tmp to old and recreate the index on the old table. We have to do this
> weekly.

Sounds like a data warehouse type problem to me, you want to load weekly records into some sort of summary table right?

Are the 1.5m records new each week, are they updated versions of old ones or is it an ever growing list? If they were really new records (you don't want the old ones) and the table is about the same size each week, I would be looking at TRUNCATE but with the KEEP STORAGE option - i.e clear out the old stuff in the most efficient way possible, but keep the storage allocation because I'm just about to fill it again. This would be especially true if there are objects that are dependent on my table. If really it is an update, the UPDATE command would seem more likely! and similarly if its an ever growing list either the INSERT or the MERGE commands would seem appropriate.

> How can we avoid to fragment the tablespace?

Make sure that it is locally managed.

> Another DBA told me there is restriction of TRUNCATE table for million
> records even I use the drop storage option. IS that true?

In a word, No. Get them to show you. If they can't show you set up a test with say 15m records (ten times your expected load). TRUNCATE will pass the test.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com