Re: fragmentation strategy for archiving question
Thank you Jack, So I guess that means our regular oltp performance will improve by archiving the old data to a different table entirely and rebuilding the indexes. It's kind of a sloppy thing though because the second you do that, new rows get added and you immediately have more than 2 years data again. I guess it would have to be a periodic archive that goes on. Floyd Wellershaus Dba/Sa Informix/Oracle/Linux/Aix http://photos.fwellers.com ======================================================== On Wed, Jan 18, 2012 at 8:50 AM, Jack Parker <jack.parker4@verizon.net>wrote: > > It depends. > > If you are querying for a large block of data, using the date column as > part of the filter, then you will get fragment elimination. > If you are querying for singleton rows using an index, then it's the index > which will be scanned, not the data fragments (except for the page(s) with > your rows). > > You will get quicker DSS queries (scan the whole table), but your OLTP > performance will not improve. > > j. > > On Jan 18, 2012, at 8:22 AM, Floyd Wellershaus wrote: > > > Hello, > > We are being asked to archive off some data from very large tables. > They want to keep 2 years worth in the main table and move the rest to > another table that may need to be queried infrequently. > > Another way of doing that seems to me that may be better, is to > partition the table(s) by the create date field, keeping 2 years worth in > the main partition and the rest in another partition. > > > > My questions are: > > 1) Will fragment elimination work with regular queries doing it this way > or do we need to make use of pdq for that ? > > > > 2) Assuming fragment elimination works, would this make for quicker > queries compared with just removing the older data from the table and > putting it in another table ? > > > > > > > > Thank you in advance, > > Floyd > > > > > > > > > > Floyd Wellershaus > > Dba/Sa Informix/Oracle/Linux/Aix > > > > http://photos.fwellers.com > > ======================================================== > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > >
On Jan 20, 1:49 pm, Floyd Wellershaus <fl...@fwellers.com> wrote: > Thank you Jack, > So I guess that means our regular oltp performance will improve by > archiving the old data to a different table entirely and rebuilding the > indexes. > It's kind of a sloppy thing though because the second you do that, new rows > get added and you immediately have more than 2 years data again. > I guess it would have to be a periodic archive that goes on. > > Floyd Wellershaus > Dba/Sa Informix/Oracle/Linux/Aix > > http://photos.fwellers.com > ======================================================== > > On Wed, Jan 18, 2012 at 8:50 AM, Jack Parker <jack.park...@verizon.net>wrote: > > > > > > > It depends. > > > If you are querying for a large block of data, using the date column as > > part of the filter, then you will get fragment elimination. > > If you are querying for singleton rows using an index, then it's the index > > which will be scanned, not the data fragments (except for the page(s) with > > your rows). > > > You will get quicker DSS queries (scan the whole table), but your OLTP > > performance will not improve. > > > j. > > > On Jan 18, 2012, at 8:22 AM, Floyd Wellershaus wrote: > > > > Hello, > > > We are being asked to archive off some data from very large tables. > > They want to keep 2 years worth in the main table and move the rest to > > another table that may need to be queried infrequently. > > > Another way of doing that seems to me that may be better, is to > > partition the table(s) by the create date field, keeping 2 years worth in > > the main partition and the rest in another partition. > > > > My questions are: > > > 1) Will fragment elimination work with regular queries doing it this way > > or do we need to make use of pdq for that ? > > > > 2) Assuming fragment elimination works, would this make for quicker > > queries compared with just removing the older data from the table and > > putting it in another table ? > > > > Thank you in advance, > > > Floyd > > > > Floyd Wellershaus > > > Dba/Sa Informix/Oracle/Linux/Aix > > > >http://photos.fwellers.com > > > ======================================================== > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text - > > - Show quoted text - correct This is expected behaviour for any "archiving" strategy like this that keeps "current" data sperately from "old" data Similarly, every week I archive my email that is over a month old, but of course that means that some data just misses the cut by a day and by the time the next arcive occurs it is over a month old. but you don't want to run the process all the time.