Re: Large Table Deletes
You can use my dbcopy utility to quickly archive the records to another table, or my ul.ec utility to archive them to a binary flat file (to be reloaded when needed using the same utility). You can also delete HUGE numbers of rows very quickly with minimum (configurable) numbers of locks using my dbdelete utility. All of these are contained in the package utils2_ak which you can download free from the Oninit web site ( www.oninit.com/utils) or the IIUG Software Repository (www.iiug.org/software ). Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jun 15, 2009 at 12:44 PM, Dave <In4MixDBA@gmail.com> wrote: > 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 >
On Jun 15, 5:58 pm, Art Kagel <art.ka...@gmail.com> wrote: > You can use my dbcopy utility to quickly archive the records to another > table, or my ul.ec utility to archive them to a binary flat file (to be > reloaded when needed using the same utility). You can also delete HUGE > numbers of rows very quickly with minimum (configurable) numbers of locks > using my dbdelete utility. All of these are contained in the package > utils2_ak which you can download free from the Oninit web site (www.oninit.com/utils) or the IIUG Software Repository (www.iiug.org/software > ). There you have it. Really the ESQL/C program isn't that difficult. And you should be able to delete lots of rows especially knowing the rowids. If you wanted to do this in an SQL program do a SELECT rowid as row_id INTO TEMP foo .... and then DELETE FROM tableA WHERE ROWID IN (SELECT row_id FROM foo) I don't know how many locks that would hold, but it should also be pretty quick. (Again if Dave can test this out on a dummy set of data, we'll know the answer.) -G
The large number of locks is why I wrote dbdelete. It holds no SELECT locks while it is deleting and no delete locks while it is selecting. It actually selects 8192 rowids (all that fit into the until recently maximum fetch buffer of 32K - that has been expanded to 4GB fairly recently and I'm in the process of updating dbdelete and dbcopy to take advantage of the increase) and uses that list to build a maximum length (64K) DELETE ... WHERE ROWID IN (...) statement and execute it. Each 8192 deletes is committed as a single transaction, so relatively few locks are held. If you try to fetch a rowid and delete it then fetch the next one etc, all while the SELECT cursor is open, your app will run MUCH slower. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jun 15, 2009 at 8:33 PM, grendal <im_gumby@hotmail.com> wrote: > On Jun 15, 5:58 pm, Art Kagel <art.ka...@gmail.com> wrote: > > You can use my dbcopy utility to quickly archive the records to another > > table, or my ul.ec utility to archive them to a binary flat file (to be > > reloaded when needed using the same utility). You can also delete HUGE > > numbers of rows very quickly with minimum (configurable) numbers of locks > > using my dbdelete utility. All of these are contained in the package > > utils2_ak which you can download free from the Oninit web site ( > www.oninit.com/utils) or the IIUG Software Repository ( > www.iiug.org/software > > ). > > There you have it. > Really the ESQL/C program isn't that difficult. And you should be able > to delete lots of rows especially knowing the rowids. > > If you wanted to do this in an SQL program do a SELECT rowid as row_id > INTO TEMP foo .... and then DELETE FROM tableA WHERE ROWID IN (SELECT > row_id FROM foo) > > I don't know how many locks that would hold, but it should also be > pretty quick. > (Again if Dave can test this out on a dummy set of data, we'll know > the answer.) > > -G > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
On Jun 15, 7:54 pm, Art Kagel <art.ka...@gmail.com> wrote: > The large number of locks is why I wrote dbdelete. It holds no SELECT locks > while it is deleting and no delete locks while it is selecting. It actually > selects 8192 rowids (all that fit into the until recently maximum fetch > buffer of 32K - that has been expanded to 4GB fairly recently and I'm in the > process of updating dbdelete and dbcopy to take advantage of the increase) > and uses that list to build a maximum length (64K) DELETE ... WHERE ROWID IN > (...) statement and execute it. Each 8192 deletes is committed as a single > transaction, so relatively few locks are held. If you try to fetch a rowid > and delete it then fetch the next one etc, all while the SELECT cursor is > open, your app will run MUCH slower. > > Art > Well slow is relative and like I said, you could always fetch the rows in to a memory buffer or buffers and then run through the buffers pretty quick. It all depends on how fancy you want to get. You could do this in parallel with each delete in a separate connection and you offset your position in the list of rowids by a large enough amount so that the rows are most likely not going to be next to each other. ESQL/C is your friend. ;-) The point is that there are many ways to slice and dice this without crushing your machine.