RE: Large Table Deletes

Right but if you fragment the table... what happens to the rowids? Date: Mon, 15 Jun 2009 12:41:15 -0500 From: jack.parker4@verizon.net To: In4MixDBA@gmail.com Subject: Re: Large Table Deletes CC: informix-list@iiug.org 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. j. On Jun 15, 2009, 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 _________________________________________________________________ Lauren found her dream laptop. Find the PC that’s right for you. http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290

Correct, as I mentioned if you use the "with rowids" clause when you fragment a table then you cannot detach fragments. If you try, then informix tells you the following: create table myfrag (fname varchar(20), num int) with rowids fragment by expression num < 5 in data_dbs_01,num >= 5 in data_dbs_02; > ALTER FRAGMENT ON TABLE myfrag DETACH data_dbs_02 myfrag2; 863: Cannot detach a table with rowids. --Dave On Jun 15, 3:30 pm, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > Right but if you fragment the table... what happens to the rowids? > > Date: Mon, 15 Jun 2009 12:41:15 -0500 > From: jack.park...@verizon.net > To: In4Mix...@gmail.com > Subject: Re: Large Table Deletes > CC: informix-l...@iiug.org > > 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. > > j. > > On Jun 15, 2009, Dave <In4Mix...@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-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list > > _________________________________________________________________ > Lauren found her dream laptop. Find the PC that’s right for you.http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290