Re: Large delete

Guess I wasn't clear enough. If you delete that many rows from a table, you will wind up with all sorts of holes in the table, the space will not be reclaimed. The run time for such a delete will be measured in tens of hours or days. If you re-write the table, the finished product will be a nice compact table - space will be reclaimed, the run time for such a delete will be measured in tens of minutes. How. 1 - set up for light/scans light appends. Boot from an onconfig with a smaller buffer setting, with as much memory as possible shoved over to SHMVIRT, and with 90% of that in DS memory. Max out your read aheads (RA_PAGES 128, threshold 120). update statistics for tables involved. 1.5 - turn logging off. 2 - build a list of the keys (in a table) you either want to delete or to keep (it doesn't really matter - best if the key table is the smaller of the keep or delete lists). 3 - create a new copy of the table (type raw if you can). Insert into that new table ... (where you built a list of the keys you want to keep) Insert into new table select a.* from old_table a, key_table b where a.key=b.key (where you build a list of keys you want to delete) Insert into new table a select * from old_table where not exists (select 0 from key_table b wherea.key=b.key); 4 - check your results (this is another advantage of this method - nothing is permanent until the next step). 5 - when complete, drop the old table and rename the new table. (If you did the type raw, don't forget to backup and alter type - also don't forget to update statistics on the new table and re-create any indices. Benchmark? Deleting 10 million rows from a 1 billion row table (using a 'delete') - over 10 hours Rewriting the 1 billion row table without the 10 million rows - 30 minutes. cheers j. -.-- --- ..- / -. . . -.. / - --- / --. . - / .- / .-.. .. ..-. . .-.-.- / ... --- / -.. --- / .. .-.-.- ----- Original Message ----- From: "Gorazd Hribar Rajteri'" <REMOVE_gorazd.hribar@telekom.si> To: <informix-list@iiug.org> Sent: Friday, October 17, 2003 6:23 PM Subject: Re: Large delete > Maybe I wasn't clear enough: our database normally runs in buffered logging > mode. When trying to delete before mentioned data from tables, I got "Long > transaction aborted" message. Then I switched to no-logging mode and the > problem is simply in duration. I cannot delete all the data necessary within > two days period. > > I'm now running unload, but since all table is too large to fit in 2 GB file > size, I have to do it partially, which causes unload to run a very long > time. I still have to see if time frame is acceptable. > > Thank you for your answer! > > Gorazd > > "Francisco Roldan" <froldan@5b.com.gt> wrote in message > news:bmp0t3$7ef$1@terabinaries.xmission.com... > > > > You can avoid the long delete duration by > > setting off logging on the database : > > > > ontape -N <DATABASE>> > > > Before doing this, check out the logingg mode > > of your database (as root): > > onmonitor -> Status -> Databases > > U= Unbuffered Logging > > B = Buffered Logging > > N = No Logging > > > > My advice is that after deleting the rows you want, > > unload the data that you still want on the database, > > and reload it . It is for avoiding the fragmentation > > caused by deleting a log of rows. > > > > To reactivate the logging on the database could be on > > two ways, depending of your choice (Buffered or Unbuffered) : > > > > BUFFERED LOGGING : > > ontape -s -L 0 -B <DATABASE>> > > > UNBUFFERED LOGGING : > > ontape -s -L 0 -U <DATABASE>> > > > > > On both options, this command will ask you to make a level 0 backup, > > but you can set up the tape device to /dev/null (not recommended) > > or actually make a real level 0 backup (recommended). > > > > For avoiding to reach the 2GB limit when unloading data you can > > use the date field on your table (if you have one, i suppose to) > > and unload the data by month for example. > > > > Or you can download the data to a named pipe and use gzip. This > > solution has been completely explained several times on this list, > > you can search on the database list archive for this explanations. > > > > > > > > Hope this help you. > > > > Regards > > > > > > > > -----Mensaje original----- > > De: Gorazd Hribar Rajteri' [mailto:REMOVE_gorazd.hribar@telekom.si] > > Enviado el: Viernes, 17 de Octubre de 2003 03:14 a.m. > > Para: informix-list@iiug.org > > Asunto: Large delete > > > > > > Hi guys! > > > > We're using IDS 7.31.FD3 on Sparc Solaris 8 (32-bit). Database design has > no > > foreign keys. All tables are nonfragmented, with dbspace scattered over > > multiple disks. Indexes are in separate dbspace. Note that upgrade is not > an > > option here. > > > > My task is to delete old invoices and related tables. In order to > accomplish > > this job, I was given identical server with up-to-date restore of > production > > database to test various strategies. > > > > The problem is: > > invoice table has 22,500,000 records; invoice lines are in two separate > > tables first having 95,500,000 records and second having 31,750,000 > records; > > both invoice lines tables are connected (in application) to general ledger > > through a separate table having 177,000,000 records. I have managed to > > delete invoices using fragmentation and detaching appropriate fragment. > > Other tables are giving me a headache. > > > > Our system is *not* 24/7 but rather 24/5 (I have two days over weekend to > do > > the job). > > > > Invoice lines tables are too big to do an unload (resulting file exceeds 2 > > GB limit). I interrupted DELETE statement on invoice lines table after > > running it for more than 40 hours. > > > > I haven't tested the following scenario: establish foreign keys > > relationships with on delete cascade option between invoice and all other > > tables, fragment invoice table to contain to-be-deleted records in > separate > > fragment and detach that fragment. > > > > ========= > > QUESTION: > > ========= > > Does anybody knows, what will happen to subordinate tables having foreign > > key constraints when fragment on invoice table will be detached? > > > > Any other ideas are greatly appreciated! > > > > Gorazd > > > > sending to informix-list > > sending to informix-list