Thanks Shawn I did think about this approach due to the disk limitation. I still want to know, if "ALTER TABLE <table-name> MODIFY <column-name> ..." can do the same for me and if it has imitations or if anyone has any suggestions of why it should not be done, especially when I am dealing with Gigs of data (more than 430 million records) - that would help! Thanks for the quick response.
Regards Aman Raheja http://www.techquotes.com On Thu, 2004-07-08 at 16:09, [EMAIL PROTECTED] wrote: > I think you may need to use a scripted approach in order to avoid > overflowing your disk. > > 1. Create a new empty table with the correct structure. > 2. Copy some "block" of records (like those with id values between 1 and > 1000) into the new table > 3. Delete the same records (the ones you just duplicated) from your > existing table > 4. Repeat from 2. but for the next block of records (1001 to 2000, 2001 to > 3000, etc) until all records have been migrated. > > Depending on the amount of free space on your disk and the size of each row > of data, you may need to adjust the number of rows you move in a "block". > > If you had the room I would say dump the table to a text file then > re-import them into the new table but I don't think that's possible in your > situation as you already said you didn't have enough space for a temp > table..... > > Yours, > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > > > > aman > > <[EMAIL PROTECTED] To: [EMAIL PROTECTED] > > es.com> cc: > > Fax to: > > 07/08/2004 04:48 Subject: re structuring tables for a > huge database > PM > > > > > > > > > > Hello All > > I have a table (about 72 GB of data). > > When the table was created, not much attention was paid to the design > aspect. > > For ex., > id int(10) should only by tinyint(3) > name char(35) should just be char(20) > and many more like that. > > I do not have enough space on the hard drive to create a temp table. > I have to re structure the table and I am seeking some suggestions. > > I plan to use > > ALTER table <tablename> MODIFY <COLUMNNAME>.... > So I would be doing > "Alter table host modify id tinyint(3) not null primary key > auto_increment" > > Are there any Alerts/Comments/Suggestions for the approach? > I have to modify almost all the pre-existing columns - is there a > quicker way? > > Regards > Aman Raheja > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
