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
================================================== ======

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:

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 (AT) verizon (DOT) net>wrote:

On Jan 20, 1:49*pm, Floyd Wellershaus <fl... (AT) fwellers (DOT) com> wrote:
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.