Re: Large Table Deletes

Thank you ALL for the excellent ideas, discussion and input.... looks like I have some great ideas to test with. Have a great day, --Dave On Tue, Jun 16, 2009 at 10:30 AM, Mario R. Canto <mcanto@4m.com.ar> wrote: > You may try a workaround: > If the table name is "thetable" > 1) RENAME thetable to thetableold, and CREATE an identical one as > thetablenew. > 2) Modify software to do INSERTs only on thetablenew and query them thru a > VIEW named "thetable" with UNION of both tables (thetableold and > thetablenew) where 1=1 > 3) When date of purging arrives, DROP thetableold (with the expired > undesired data inside it), RENAME thetablenew to thetableold, and CREATE > thetablenew, keeping the VIEW. > > HTH > > Mario R. Canto > > > On Jun 15, 2009, Informix DBA <in4mixdba@gmail.com> wrote: > >> 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-