Niall Litchfield wrote:
> "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.
>
>
Thanks for your detailed suggestion. Yes it is a total new records set
each week. The manager just wants to toast away old ones. However, you
mentioned "if the table is about the same size". At this point we don't
know. It may increase along with the time. It is some sort of customer
customer invoices records.
>>How can we avoid to fragment the tablespace?
>
>
> Make sure that it is locally managed.
>
The tables are defined in such way.
>
>>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.
>
>
Senior > Junior > helper (I am in this level)
will do whatever Seinor told but suspect. Therefore I asked question
around.
C Chang
Received on Sat Mar 12 2005 - 07:30:23 CST
