RE: Large Table Deletes

Dave, You could do this in 4GL. I'm not sure what isolation level you are running but your select statement should not hold a lock. (Dirty Read) Again if you do the DELETE outside of a transaction, it will be an atomic statement (meaning each delete would be its own transaction. So any locks would be on the row as it gets deleted) Locking shouldn't be an issue, however if you need the data, it will be gone and you can't get it back. The 4GL wouldn't be as fast as an ESQL/C program but it would be easier to write and probably fast enough. Date: Mon, 15 Jun 2009 16:07:18 -0400 Subject: Re: Large Table Deletes From: in4mixdba@gmail.com To: im_gumby@hotmail.com CC: jack.parker4@verizon.net; informix-list@iiug.org So in terms of which language, I usually only use UNIX scripting.... The row size for this table is 138.... In terms of rebuilding the table each month, well that would mean an outage each month and it would take time (even with PDQ) to rebuild indexes, constraints etc.. etc... and I could then simply lock the whole table and run the delete and that would be faster... Though I don't want to lock the whole table.... For the initial delete, I actually did that already by doing as you mentioned and rebuild the table with only the 6 months of data that I wanted to keep. Though I had to schedule an outage for that operation. What I need is a good strategy for doing this on an ongoing monthly basis, with as a best scenario not scheduling a monthly outage (if possible). I could try something like you mentioned about using a temp table to control deletes... though I think it would still allocate lots of locks this way??? > 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) I am not fluent yet in ESQL/C or python.. though one of our 4GL developers offered to write something if needed.... --Dave On Mon, Jun 15, 2009 at 3:08 PM, Ian Michael Gumby <im_gumby@hotmail.com> wrote: 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. Check it out. _______________________________________________ Informix-lis