Re: Partitioning an Existing table in Oracle 9.2.0.7.0

sangu_rao_at_yahoo.co.in schrieb:
> Hi,
> Thanks for the reply. I have created the partitioned table exactly the
> same way as source table with all indexes. Still the exchange is not
> happening with the indexes. I am giving the example code which i have
> tried.
>
> --- Source table
> CREATE TABLE TEST(A INT PRIMARY KEY,B VARCHAR2(10),C DATE);
>
> INSERT INTO TEST VALUES(1,'2004',TO_DATE('01-10-2003','DD-MM-YYYY'))
> INSERT INTO TEST VALUES(2,'2005',TO_DATE('01-10-2004','DD-MM-YYYY'))
> INSERT INTO TEST VALUES(3,'2006',TO_DATE('01-10-2005','DD-MM-YYYY'))
> INSERT INTO TEST VALUES(4,'2007',TO_DATE('01-10-2006','DD-MM-YYYY'))
> COMMIT
>
> -- Partitioned table
>
> CREATE TABLE TEST2 (A INT PRIMARY KEY,B VARCHAR2(10),C DATE) PARTITION
> BY RANGE (C) (PARTITION PART2004 VALUES LESS THAN (MAXVALUE));
>
> -- Then tried to exchange
>
> ALTER TABLE TEST2 EXCHANGE PARTITION PART2004 WITH TABLE TEST WITHOUT
> VALIDATION.
>
> -- The statement works and i am able to query the table test2. But when
> do any Inserts it is throwing the error " index or partition of such
> index is in unusable state"
> The after rebulding the index it is working. But as i said my actual
> table contains millions of rows and lot of indexes this rebuilt will
> take long time. When i tried with the option Including the indexes it
> is not working i.e.
>
> ALTER TABLE TEST2 EXCHANGE PARTITION PART2004 WITH TABLE TEST INCLUDING
> INDEXES WITHOUT VALIDATION.
>
> -- The above statement is not working it is throwing the error
>
> ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
>
> Why so.Eventhough I have the same index and table structure for both
> source and partitioned table.
>
> Could you please suggest me what could be the best way to EXCHANGE the
> data and the indexes and constraits from source table to partitioned
> table without rebuild.
>
> Thanks
> Rao
>
>

You can exchange including indexes only if your indexes on the single table correspond to the *local partitioned* indexes on the partitioned table. Said that, you would fail to make your primary key underlying index partitioned local because to be able to do that, index *must* contain the partitioning key, which is not your case. Moreover, to explain it here doesn't really make sense, because it is pretty comprehensive explained at
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#i1108745 and
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/parpart.htm#i1007993 ( you can look also for "UPDATE INDEXES" clause and decide whether it might be suitable for your conditions).

Best regards

Maxim Received on Tue Jan 09 2007 - 02:10:14 CST