Re: Using Partitions With Date Range Question

In article <alv25.822$j7.40494_at_news.bc.tac.net>,   "Jake de Haan" <jake_dehaan_at_telus.net> wrote:
> I've got a transaction table that we'd like to only keep the last 13
 months
> for and thought that partitions would be the way to set this up (i.e.
 each
> partition would contain a month's worth of data and we could drop the
 oldest
> partition each new month).
>
> 1.Is there any performance loss if I set up monthly partitions for
 the next
> 5 years or so? These partitions would be empty for some time, so
 would it
> be better to set them up on a month-to-month basis?
>
> 2. Is there a performance loss if a query goes across 2 or more
 partitions?
> I'm hoping that the answer is no and that this would be the same as
 hitting
> 2 or more extents but I can't seem to get much info on this.
>
> 3. Is this the best way to do this?
>
>

Jake,

You asked

> 1.Is there any performance loss if I set up monthly partitions for
 the next
> 5 years or so? These partitions would be empty for some time, so
 would it
> be better to set them up on a month-to-month basis?

The only thing you will be consuming is the initial space for each empty partition. Go ahead and set them up presuming you have enough space to do so; performance should not suffer.

> 2. Is there a performance loss if a query goes across 2 or more
 partitions?
> I'm hoping that the answer is no and that this would be the same as
 hitting
> 2 or more extents but I can't seem to get much info on this.

The only performance loss you should experience is on queries that do not utilize the local prefixed index on the partitioned table. Then every partition will be scanned for the query data, and if you have 60 partitions that could take a while. Even the empty partitions will be accessed, and though it won't be a large portion of time in itself scanning 60 partitions when only 2 contain pertinent data is a waste of resources.

> 3. Is this the best way to do this?

In my opinion, maybe. Remember that the lower limit of a partition is the upper limit of the previous partition, and that the first partition will not have a lower limit, just an upper limit. This can allow a situation like the following example to occur:

Table X is partitioned on txn_dt
12 partitions exist
Partition X_first_mth has an upper limit of February 1, 2000 ...
Partition X_twelfth_mth has an upper limit of January 1, 2001

Any value less than February 1, 2000 will be inserted into partition X_first_mth, so records from 1999, 1998, 1997, etc. will be located with the January, 2000 records if they are inserted into table X.

One way to correct this it to create a dummy partition as the first partition to hold all records earlier than, say, January 1, 2000. Table X would then look like:

Partition X_all_others has an upper limit of January 1, 2000 Partition X_first_mth has an upper limit of February 1, 2000 ...
Partition X_twelfth_mth has an upper limit of January 1, 2001

Now the partition X_first_mth will hold only records from January 1, 2000 through January 31, 2000 and any records older than January 1, 2000 would end up in X_all_others. Unfortunately this only works until you drop the X_first_mth partition; at that point all records between January 1, 2000 and February 29, 2000 will end up in partition X_second_mth; the X_all_others upper bound now becomes the lower bound for partition X_second_mth. As monthly partitions are deleted the lower bound for the remaining lowest monthly partition becomes January 1, 2000. If you have no dummy partition to enforce a pseudo lower limit then once a monthly partition is dropped the lowest monthly partition remaining then has no lower bound and becomes a 'catch-all' for any data records dated earlier than the partition upper limit.

Another possible solution to this dilemma is to put a trigger on the table to 'weed out' any records with dates less than the implied lower limit.

Think carefully and plan well if you implement this strategy.

David Fitzjarrell
Oracle Certified DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 16 2000 - 00:00:00 CDT