Re: Moving data between tables

In article <3ot9kl$mld@CUBoulder.Colorado.EDU>, sautins@spot.Colorado.EDU says... > > > We are currently using Informix 7.1 on Solaris >and are having the following problem. Our application >populates a table with data during the day and then, once >the application has finished processing the data, needs to >move the records into another table for storage (30 days >of history). The problem is that during the day, between >2-4 million records are inserted into our daily table and >moving those records to the history table takes a excessive >ammount of time (i.e., over 24 hours). > > My question is, what is the best way of moving data >from one table to another in the fastest possible manner? >I've heard about detaching and attaching fragments from one >table to another, but our problem seems to be that the processing >can fall behind the population, so using a set number of fragments >may not be possible. Can I attach a table to another? Is there >another solution? > > > Thanks > -drew > sautins@coralsys.com Apologies in advance if I am teaching you to suck eggs. I presume the reason it takes over 24 hours to load data that was initially inserted in just 8-12 hours is because the target table has got 2-4 million * 30 records in it. How long does it take to rebuild your indexing on the target table? Generally speaking it is quicker to drop the indexes, load, then rebuild indices, then it is to load and index on the fly. This has an added benefit of allowing you to set your index fillfactor to 100% every day meaning that reads of your history table will never be skipping empty slots. Have you considered also some kind of asynchronous process to copy across your records as they are entered or on a timed basis. I do something like this on a much smaller scale by including an archive indicator in the table definition which is null until I set it Cron runs the unload this every night in the early hours setting the archived indicator and populating our DSS system. I do it once a day but there is nothing to stop it happening every hour.