Help: Re-Oraganizing a table

From: Mike F <u518615722_at_spawnkill.ip-mobilphone.net>
Date: Tue, 02 Apr 2002 20:11:17 GMT
Message-ID: <l.1017778277.1333984375@[64.94.198.252]>

I am writing a perl script which will re-organie a table by the primary  key. I have two options

First,

a)create the temp table as select * from origianl table
b)drop all the indexes on the original table
c)disable all the child table foreign constraints related to this table
d)truncate the original table
f)re-insert all the rows from that temp table
g)re-create all the indexes
f)re-enable all the child table constraints

This approach works fine, the problems is, for some big tables with 1 million row, the data is not available for about 30 minutes.

Second approach

a)create the temp table as select * from origianl table
b)create all the indexes on the temp table
c)find those child table, and add foreign constraints pointing to the 

temp table
d)create all the comments on the temp table and columns f)rename original table to temp and rename the temp table to target table

this approach, data is always available to users,

my question is, for my second approach, did I list everything I have to  worry about?

What will happen to the triggers on the table? Will it point to the new table or will it point to the original table which is now renamed to temp? Is there anything else I need to worry?

Thanks  

-- 
Sent by  dbadba62  from  hotmail in area com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new

Received on Tue Apr 02 2002 - 14:11:17 CST