Re: Optimization issues

From: Martin Drautzburg <drautzburg_at_altavista.net>
Date: 08 Apr 2001 00:45:01 +0200
Message-ID: <87pueoib2q.fsf@altavista.net>

Ismael Cortés <icortes_at_opengets.cl> writes:

> Another approach is to partition tables, but all tables will need a
> "separation attribute", and we need also a fix-process to set the
> separation attribute. We also needs to update all queries to be
> suitable for the new table-structure, and reconsider all indexes.

Why not just partition by date ? You can e.g. use one partition for each month. If all your queries only look at the last two months, oracle's partition pruning will only look at 3 partitions the most. This is at least true for simple queries.

You may also have to make all your indexes LOCAL so they are partitioned too.

AFAICS this does not require any redesign, since you no doubt have a date column already. Also you can get rid of historic data easily by simply truncating or deleting some partitions. You will however have to create new partitions at least once a month. Received on Sat Apr 07 2001 - 17:45:01 CDT