Re: Large delete
Hello to all of you! Thank you very much for all the suggestions I've got. I decided to combine HPL (for large tables) and table rewriting (for smaller tables) to do the job. Benchmarks show, that I will be able to do it in less than 24 hours. By using HPL to unload, recreate tables/indexes and load back data that remains (about 1/3 of total data), tables will end up defragmented as "side effect" - nice! Again - Thanks! Gorazd "Gorazd Hribar Rajteriďż˝" <REMOVE_gorazd.hribar@telekom.si> wrote in message news:UoOjb.4638$2B6.843551@news.siol.net... > 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 >