CODEWITHSUNDEEP
oracle-databasedatabase-partitioningdatabase-indexes
punita.gosar
punita.gosar

Reputation: 400

Exchanging Range-Partitions with Local Indexes

We have tables with range partitions of a month interval. The indexes on the tables including the primary key are local indexes.

However, when exchanging the partition from these tables with a table without partition, the local indexes become UNUSABLE. In the Oracle documentation, it only talks about issues with Global indexes. So we converted the global primary indexes to local by adding the partitioned key to the index.

Example queries - 

CREATE TABLE sourcetable
 (owner varchar2(30), OBJECT_NAME VARCHAR2(100))
 PARTITION BY RANGE (owner) 
 (PARTITION P1 VALUES LESS THAN (MAXVALUE));

CREATE TABLE DESTTABLE
 (owner varchar2(30), OBJECT_NAME VARCHAR2(100));

create index sourcetable_idx 
on sourcetable ( owner )
local ;

alter table sourcetable add constraint src_pk primary key ( owner );

insert into sourcetable select U.OBJECT_ID, u.object_name from user_objects u
 where rownum <11;

select index_name,partition_name,status from user_ind_partitions
 where index_name IN (
select index_name from user_indexes
 where table_name = 'SOURCETABLE'
);

The select gives the index status as

USABLE

ALTER TABLE sourcetable 
EXCHANGE PARTITION p1 WITH TABLE desttable;

select index_name,partition_name,status from user_ind_partitions
 where index_name IN (
select index_name from user_indexes
 where table_name = 'SOURCETABLE'
);

The select on index status after exchanging the partition gives the index status as

UNUSABLE

We want to do online partition exchange. Is there any way we can do the exchange using statements like

UPDATE INDEXES

with the

EXCHANGE PARTITION

statement?

We tried using EXCHANGE statement with UPDATE INDEXES, but the indexes still became UNUSABLE.

ALTER TABLE sourcetable 
EXCHANGE PARTITION p1 WITH TABLE desttable UPDATE INDEXES;

Upvotes: 0

Views: 920

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59553

UPDATE INDEXES applies only for global indexes. Since you don't have any, this clause has no effect.

You must use INCLUDING INDEXES. However, for this you must create the same indexes on source and destination table. Try this one:

CREATE INDEX desttable_idx ON desttable ( OWNER );
ALTER TABLE sourcetable EXCHANGE PARTITION p1 WITH TABLE desttable INCLUDING INDEXES;

Upvotes: 0

Related Questions