Re: Partitioning problems - Oracle 8.1.7.4
- From: Tim Gorman <tim@xxxxxxxxx>
- To: <Tony.Adolph@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 05 Feb 2005 10:36:27 -0700
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@xxxxxx at Tony.Adolph@xxxxxx 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
> */
--
//www.freelists.org/webpage/oracle-l
Other related posts:
