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 dataand
indexes the same using date.

When combining oltp and dss into a single table, chances are your goingto
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 combiningall
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 (AT) iiug (DOT) org
To: informix-list (AT) iiug (DOT) org
Date: 01/18/2012 09:00 AM
Subject: Informix-list Digest, Vol 308, Issue 1
Sent by: informix-list-bounces (AT) iiug (DOT) org



Send new Informix-list mailing list postings to
informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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 (AT) fwellers (DOT) com> on Wed, 18 Jan
2012 08:22:52 -0500 -----

To: informix-list (AT) iiug (DOT) 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 (AT) verizon (DOT) net> on Wed, 18 Jan
2012 08:50:10 -0500 -----

To: Floyd Wellershaus <floyd (AT) fwellers (DOT) com>

cc: informix-list (AT) iiug (DOT) 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:

another table that may need to be queried infrequently.
partition and the rest in another partition.
putting it in another table ?