Re: Large Table Deletes
<p>We fragment by date, then detach and drop fragments which are too old. Takes less than a second. Does require a lock on the table (check into DIRTY_WAIT), constraints are difficult to work with, you have to be careful with indices.</p><p><br />j.<br /></p><p>On Jun 15, 2009, <strong>Dave</strong> <In4MixDBA@gmail.com> wrote: </p><div class="replyBody"><blockquote style="padding-left: 1ex; margin: 0pt 0pt 0pt 1.8ex; border-left: #267fdb 2px solid">Hello,<br /><br />I am looking to archive/delete on a monthly basis about 2 million rows<br />from a 10 million row table. There is a date field that will be used<br />to drive the delete for records older than 6 months. The table has a<br />growth rate of about 2 million rows a month. For this particular<br />table, data is only added and is never modified or deleted by a user.<br /><br />Currently we are on IDS 10.00.FC6 and will be upgrading to 11.50.FC4<br />within the next few months.<br /><br />Our legacy application uses rowids (not recommended, we know) and if<br />you fragment a table using the "with rowids" clause, then you cannot<br />detach fragments. I would like to be able to do this delete while<br />keeping the server on-line and not locking the whole table. Also,<br />although lock allocation is dynamic, running this delete would just<br />keep allocating locks and I would end up with a whopping 11.6 million<br />locks (I tried it in a test environment - not cool).<br /><br />Any good ideas on how to run this delete?<br /><br />--Dave<br />_______________________________________________<br />Informix-list mailing list<br /><a href="mailto:Informix-list@iiug.org" target="_blank" class="parsedEmail">Informix-list@iiug.org</a><br /><a href="http://www.iiug.org/mailman/listinfo/informix-list"; target="_blank" class="parsedLink">http://www.iiug.org/mailman/listinfo/informix-list</a><br /></blockquote></div>