Re: Optimization issues

Thank you Martin for your answer. It seems the better solution, but it forces to create a new partition (cause the time goes on), every time, and it cannot be done automatically (the system is not under our supervision, it's for a client). Also, we have to change access methods in ejb's to retrieve only last months.
The main problem is that the partitions are not "mobile", for example, with a variable atribute.

I will try with partitions, and try to generate an automatic process to create new ones.

Martin Drautzburg wrote:

>
> 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 Mon Apr 09 2001 - 08:44:34 CDT