Re: Table Partiotioning of Oracle 8

Hi,

I would create quarterly partitions and not worry about merging them as time goes by. Your expected volume per quarter is probably predictable, so space management should not be a major concern. I would also look into a plan to age-off some of the old data. Most "users" always want all of the data, but that is not practical, especially when a large volume of data exists.

Of course, don't partition just for the sake of doing it. Partition to exploit parallel processing, manage volumes, tune access to specific months, when a majority of queries can be satisfied from within a single partition, allow one partiton to be aged or backed up independently of others, etc.

Although this example does not solve your problem of moving data to the older partition, it is an example of merging partitions. It is straight from the O8 administrators manual.

1. Create an empty temp table to hold FEB96 data.
2. Exchange the FEB96 partition segment into TEMP_FEB96 table. No data is actually moved, just a "pointer". alter table FEB96 exchange partition FEB96 with table TEMP_FEB96 without validation;
3. Drop the FEB96 partition.
4. Move the data from TEMP_FEB96 table into the MAR96 partition via an insert statement. insert into SALES partition MAR96 select * from TEMP_FEB96;
5. Drop TEMP_FEB96 table.

By default the partition with a high range value of FEB96 no longer exists and the data goes into the MAR86 from now on.

Jeff Cohen
Principal Consultant
BREZE, Inc.
jcohen_at_breze.com

Narayanan Olagappan wrote in message <368323EA.471276D9_at_sprynet.com>...
>Hi guys,
>
>I have a problem here, any help will greatly be appreciated.
>
>We have database not so large, but my manager has hooked to the idea of
>partitioning the tables. Our partitioning has to be on dates. I referred
>to Oracle manual, some white papers, and news group, everyone talks and
>praises such temporal partitioning that can be achieved in Oracle. But
>they all say the same, create a new partition for every one month, three
>months or whatever, and drop the oldest. Unfortunately, in my case I can
>not drop the oldest, it needs to be there as long the users want it. If
>I create a partition every month, I will end up with hundreds of
>partition. The only feasible solution is have different partitions for
>the last three months and have one partition for the rest (earlier
>months) . (The partition that has old data will be huge compared to
>others, which is all right for us as the older data is hardly accessed,
>and most of the queries are on date.) This I can achieve by creating a
>new partition every month and merge the three months old partition to
>the one that holds the older data. I looked up the manual, there is
>split, drop, truncate and modify, but there is no merge. OK, I will (do
>it myself) drop the third month partition, after backing the data up,
>and load it into the older data partition: this is not possible because
>the third month data will violate the partition definition of the older
>data partition, and I have not come across any syntax in the manual that
>allows changing the partition definition. The sure way, of course, is
>backup the table, drop, and recreate with new partition definition, and
>load the data back, but this has a hitch, we have quite a few tables
>with few million rows and repartitioning them in the allowed down time
>may not be possible. This is my problem, how do I achieve such
>partitioning without the nightmare of recreating the entire table.
>
>If anyone can help me in this, I will greatly appreciate it.
>
>Thank you very much.
>
>
>
Received on Fri Dec 25 1998 - 09:53:13 CST