Re: Optimization issues

Ismael Cortés <icortes_at_opengets.cl> writes:

> 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).

You can create a job that runs once a month (or several times a month ) and creates next month's partition it it isn't there yet. You need dynamic SQL to do this. Not too difficult though.

Another way of doing this is to have a fixed number of partitions, again one for each month, but will a limited total capcity of say 5 years. You can then create all the partitions initially and reuse old partitions for newer data (sort of round robin). Then however you cannot use the MONTH as the partiotion key, but you need to map the MONTH to an integer (lets call it the PARTITION_NR) that is always in the range of 5 (years) *12 (moths/year) = 1..60. Also your queries need to include a "where partition_nr in ..." clause or you won't get partition pruning, because Oracle does not know how you mapped your MONTH to PARTITION_NR.

> Also, we have to change access
> methods in ejb's to retrieve only last months.

If the month (or the date) is the partitioning key, there should not be much left to do. Of couse you have to have some date restrictions in your where clause, but I bet you have this already.

> The main problem is
> that the partitions are not "mobile", for example, with a variable
> atribute.

Not sure what you mean by this Received on Tue Apr 10 2001 - 20:18:39 CDT