Re: Informix-list Digest, Vol 308, Issue 1

I'd like to expand on Jack's comment and the 'it depends' comment. If all your queries include a date (dss and oltp), then fragmentation by date is a great way to store the data. You'll want to fragment the data and indexes the same using date. When combining oltp and dss into a single table, chances are your going to have some queries that won't have date available for a predicate which means you'll have indexes which aren't fragmented by date. By combining all the data into a single table, the indexes will also be larger which means the btrees will be deeper. This could have some impact on query performance because btree traversals will have to go down more levels of the tree which not only makes the code path to get to the leaf page to retrieve the key longer, it also means more pages in your bufferpool cache to hold the index since its larger. Just more food for thought. Mr.GrumpyPants From: informix-list-request@iiug.org To: informix-list@iiug.org Date: 01/18/2012 09:00 AM Subject: Informix-list Digest, Vol 308, Issue 1 Sent by: informix-list-bounces@iiug.org Send new Informix-list mailing list postings to informix-list@iiug.org If replying to a posting, the message number must appear on the Subject line. This will maintain the discussion thread. The message number is inside square brackets (e.g. [102]). You may edit the rest of the Subject line. To subscribe or unsubscribe log in to the Member Area of http://www.iiug.org and click on "Subscribe to E-mail Lists". You can reach the person managing the list at informix-list-owner@iiug.org Today's Topics: 1. fragmentation strategy for archiving question (Floyd Wellershaus) 2. Re: fragmentation strategy for archiving question (Jack Parker) ----- Message from Floyd Wellershaus <floyd@fwellers.com> on Wed, 18 Jan 2012 08:22:52 -0500 ----- To: informix-list@iiug.org Subject: fragmentation strategy for archiving question 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 ======================================================== ----- Message from Jack Parker <jack.parker4@verizon.net> on Wed, 18 Jan 2012 08:50:10 -0500 ----- To: Floyd Wellershaus <floyd@fwellers.com> cc: informix-list@iiug.org Subject: Re: fragmentation strategy for archiving question 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