Re: Partitioning problems - Oracle 8.1.7.4

Tony,

The UPDATE GLOBAL INDEXES clause became available with Oracle9i; it is not available in Oracle8i (8.1.7.4).

The second problem is a little more subtle...

When using the INCLUDING INDEXES clause, you are not supposed to have indexes on the temporary table that match the definition of the GLOBAL indexes on the partitioned table. From a mechanical perspective, be aware that the ALTER TABLE ... INCLUDING INDEXES clause is using the list of indexes on the temporary table to match to the list of local indexes on the partitioned table, so the error message is indicating that you do not have a corresponding local index to the UNIQUE index on the temporary table.

Now, why should this be so? The fact is, an index on the temporary table is equivalent, UNIQUE or NONUNIQUE, only to a LOCAL index on the partitioned table. Even though you have used the same column definitions on both sides, the two index (PK_TONYTAB and PK_TONYTAB_TEMP) are entirely different types of indexes. It may seem that Oracle is nit-picking, but they are doing so in a good cause. So, get rid of the PK_TONYTAB_TEMP index and it should work...

I have another question, more as food for thought...

Why do you have a separate "archive" table at all? What is wrong with just leaving the data all within the same table? If people don't want to query old data, then presumably they will add appropriate phrases to their queries so that they don't. With partition-pruning, the queries will not even traverse the unneeded partitions.

Just something to consider, in the spirit of the old vaudeville routine that goes something like:

    Patient: Doctor! Doctor! It hurts when I do this! (Waves his arm

              wildly and comically)
    Doctor: Then don't do that.

Hope this helps...

-Tim

on 2/4/05 3:02 AM, Tony.Adolph_at_o2.com at Tony.Adolph_at_o2.com wrote:

> /*
> Hi Oracle partitioning gurus,
> I am trying to setup a group of tables partitioned on a date column so
> that monthly
> a new partition can be added and the oldest partition can be archived.
>
> I have created 2 extra tables for each main table. One is a temporary
> table that is used
> to swap partitions in an out of and a 3rd table, the archive.
>
> I have tried to use local indexes where possible to simplify the exchange
> operation, ie.
> reduce the work oracle has to do. But I have had to create a global index
> for the PK index
> as the range column is not in the PK. See below for example setup.
>
> I have written a couple of SPs, one to add a new partition and the other
> to swap the oldest
> partition from the main table to the archive via temp.
>
> Problems:
>
> 1. When adding new partition:
> ------------------------------
> I understood that to stop any global index going 'UNUSABLE' when adding a
> new partition
> I must use something like:
> alter table TONYTAB
> split partition P04 at (to_date('200504','yyyymm'))
> into (partition P04, partition p05)
> UPDATE GLOBAL INDEXES;
> But I get this error:
> ORA-14126: only a <parallel clause> may follow description(s) of resulting
> partitions
>
> When I drop the "UPDATE GLOBAL INDEXES" it seems to work without
> invalidating the indexes.
>
> What is going wrong here? Is it because I have global and local indexes
> on the same table?
>
> 2. Exchanging partitions:
> --------------------------
>
> To swap the oldest partition from my main table my SP tries to do this:
> alter table TONYTAB
> exchange partition P01 with table TONYTAB_TEMP
> including indexes without validation;
> alter table TONYTAB_ARC
> exchange partition P01 with table TONYTAB_TEMP
> including indexes without validation;
>
> But I get this error at the first exchange:
> ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
>
> I tried to drop the temp table's indexes but get the same error.
>
> I can see that the index I_TONYTAB_1 is local for the main table and is a
> normal index
> for the temp table, but thats it. I can't be local for the temp table is
> its un-partitioned.
>
> I've read Lewis Chpts 12 & 13 a couple of times now but seem to be missing
> something
> somewhere.
>
> Any help would be greatly appreciated.
>
>
> Cheers
> Tony
> */

--
http://www.freelists.org/webpage/oracle-l