Re: Partitioning problems - Oracle

  • From: Tony.Adolph@xxxxxx
  • To: tim@xxxxxxxxx
  • Date: Mon, 7 Feb 2005 13:59:36 +0100

So in my last post everything was accurate apart from the text!  I had 
meant to write "I only have an index corresponding to the global 
index....." but even got that wrong.  Mondays!
Thanks for the sound advise and pointers.


Tim Gorman <tim@xxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/07/2005 12:58 PM
Please respond to

"Oracle L (E-mail)" <oracle-l@xxxxxxxxxxxxx>

Re: Partitioning problems - Oracle

> When I drop the global index on my temp table, I get the same error, so 
> dropped the local index and recreated the global.

You don't have "global" and "local" indexes on your non-partitioned
temporary table.  You have indexes on the temporary table which correspond
to the global and local indexes on your partitioned table.

> So on my temp table I now only have the global index:
> SQL> create index I_TONYTAB_TEMP_1 on
> 3    tablespace PAGENTX00 pctfree 10 initrans 2 maxtrans 255
> 4    storage (initial 1k next 1k minextents 1 maxextents 121 pctincrease
> 0);
> Index created

No, this is the index on TONYTAB_TEMP which corresponds to the LOCAL index
on TONYTAB, not the GLOBAL.  Remember, you do not have LOCAL and GLOBAL
indexes on non-partitioned tables.

> SQL> alter table TONYTAB exchange partition P01 with table TONYTAB_TEMP
> including indexes without validation;
> Table altered
> Which seems to be opposite to what you are suggesting :-(

No, this is exactly what I was saying.  The use of the INCLUDING INDEXES
means that you can only exchange indexes corresponding to LOCAL indexes.
Having indexes corresponding to GLOBAL indexes will cause an error, 
even though they reference the same columns, they do not function the same



Other related posts: