RE: Large Table Deletes

Deleting 2 million rows based on known row ids? Since 'Dave' has the data, he could try it. I mean really, he's kinda screwed because he can't frag the table. What you're suggesting is to alter the table by renaming it, recreate the table and copy over the 8 million good rows? (I'm assuming that's what you meant by rewriting the table, or am I mistaken?) That would mean you would have to stop access to the database while you do this, right? The SELECT could be relatively fast if you create an index based on the date or time stamp column, then you're not doing a sequential scan. In addition, only the first delete would be 'slow' because you would be deleting all data that's older than 6 months. So the second month that you run this, you'll only have 1 month of data that is being deleted. Assuming a constant growth rate, 2 million rows. I don't know what he's using for hardware, but 2 million rows? (Again we don't know the row size) I'd say he could have a python script or ESQL/C program do that in under 20-30 minutes but lets say an hour. Now correct me if I'm wrong, but isn't rowids mapped to the physical offset within the table? So access would be fast, right? Also assume a row id is the length of a word. That would mean that your program would have to store 2 million words in memory. (4 bytes per word?) 8MB for the array that you can walk sequentially? Still relatively efficient. (Assuming a script.) [ok, 8MB + 4 bytes for the pointer the malloc()'d memory and 4 bytes for the pointer to walk through the memory.] Ok am I correct in saying that the rowid is 4 bytes long or is it 8 bytes long. It doesn't really matter since it will only double the size of the malloc from 8mb to 16 mb. Its a prepared statement, right? Run it in the background and you should hardly notice it running? I mean there's no window of work, right? Or what am I missing? -G > Date: Mon, 15 Jun 2009 13:30:42 -0500 > From: jack.parker4@verizon.net > Subject: Re: RE: Large Table Deletes > CC: informix-list@iiug.org > > > This is still a multi-tens_of_minutes solution. If not using a fragmentation strategy, it would be faster to re-write the table than delete 20% of it. > > j. > > On Jun 15, 2009, Ian Michael Gumby wrote: > > > > > > > > Ok, > > You didn't mention what language you wanted to do this in... > > Since you're testing, can you try these ideas out? > > If you do not need to do this in a transaction then each delete could be a singleton and atomic statement. > Try something like this... > (This isn't executable code) > curs = SELECT rowid FROM table_name WHERE date < xxxx > Foreach row in curs: > DELETE FROM table_name WHERE rowid = row.rowid > End Foreach> > Depending on the language, this could be done quickly. > > > Or you could try ... > (Again the syntax isn't accurate) > > SELECT rowid AS row_id > INTO TEMP foo > FROM table_name a > WHERE a.date < xxx> > DELETE FROM table_name WHERE rowid IN (SELECT row_id FROM foo)> > [This may reduce your locks and speed things up.] > > HTH > > -G > > > > From: In4MixDBA@gmail.com > > Subject: Large Table Deletes > > Date: Mon, 15 Jun 2009 09:44:57 -0700 > > To: informix-list@iiug.org > > > > Hello, > > > > I am looking to archive/delete on a monthly basis about 2 million rows > > from a 10 million row table. There is a date field that will be used > > to drive the delete for records older than 6 months. The table has a > > growth rate of about 2 million rows a month. For this particular > > table, data is only added and is never modified or deleted by a user. > > > > Currently we are on IDS 10.00.FC6 and will be upgrading to 11.50.FC4 > > within the next few months. > > > > Our legacy application uses rowids (not recommended, we know) and if > > you fragment a table using the "with rowids" clause, then you cannot > > detach fragments. I would like to be able to do this delete while > > keeping the server on-line and not locking the whole table. Also, > > although lock allocation is dynamic, running this delete would just > > keep allocating locks and I would end up with a whopping 11.6 million > > locks (I tried it in a test environment - not cool). > > > > Any good ideas on how to run this delete? > > > > --Dave > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > Hotmail® has ever-growing storage! Don’t worry about storage limits. Check it out. > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Windows Live™: Keep your life in sync. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_BR_life_in_synch_062009