Index rebuild on exchange partition

[pranavgovind]

Dear All,

I have one partition table and one non partition table. I am exchanging partition table into non partition table. When i exchange, i am using UPDATE GLOBAL INDEX key word. It rebuilds the index for partition table. But still the non partition table index become UNUSABLE. I am manually rebuilding this non partition table index for every parition exchange. I wanted to rebuild the non partition table index during my partition exchange. Is it possible? Please advise.


SQL>
SQL> CREATE TABLE sourcetable
2 (OBJECT_NAME VARCHAR2(100))
3 PARTITION BY RANGE (OBJECT_NAME)
4 (PARTITION P1 VALUES LESS THAN (MAXVALUE))
5 /

Table created.

SQL>
SQL> CREATE UNIQUE INDEX SOURCETABLE_IDX ON sourcetable
2 (OBJECT_NAME)
3 /

Index created.


SQL>
SQL>
SQL>
SQL> CREATE TABLE DESTTABLE
2 (OBJECT_NAME VARCHAR2(100))
3 /

Table created.

SQL>
SQL> CREATE UNIQUE INDEX DESTTABLE_IDX ON desttable
2 (OBJECT_NAME)
3 /

Index created.

SQL>
SQL> insert into sourcetable select object_name from user_objects
2 where rownum < 5;

4 rows created.

SQL>
SQL> select index_name,status from user_indexes
2 where table_name in('SOURCETABLE','DESTTABLE')
3 /

INDEX_NAME STATUS
------------------------------ --------
DESTTABLE_IDX VALID
SOURCETABLE_IDX VALID

SQL>
SQL> ALTER TABLE sourcetable EXCHANGE PARTITION p1 WITH TABLE
2 DESTTABLE update global indexes
3 /

Table altered.

SQL>
SQL> select index_name,status from user_indexes
2 where table_name in('SOURCETABLE','DESTTABLE')
3 /

INDEX_NAME STATUS
------------------------------ --------
DESTTABLE_IDX UNUSABLE
SOURCETABLE_IDX VALID

[mike9]

is it on Oracle 8.0.3 or 8.1.5 (8i) ?

[ixion]

If you use local indexes instead of a "global" index the problem will go away. And there is no need to "rebuild" the global index.

[pranavgovind]

Mike/Ixion,

here is the verision i am using.

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

I have a problem on two scenarios.

Scenario I
==========

Source table is partitioned table and target is not a partitioned table.


SQL> drop table sourcetable;

Table dropped.

SQL> drop table desttable;

Table dropped.

SQL>
SQL> CREATE TABLE sourcetable
2 (OBJECT_NAME VARCHAR2(100))
3 PARTITION BY RANGE (OBJECT_NAME)
4 (PARTITION P1 VALUES LESS THAN (MAXVALUE))
5 /

Table created.

SQL>
SQL>
SQL> CREATE TABLE DESTTABLE
2 (OBJECT_NAME VARCHAR2(100))
3 /

Table created.

SQL>
SQL>
SQL> CREATE INDEX DESTTABLE_IDX ON desttable
2 (OBJECT_NAME)
3 /

Index created.

SQL>
SQL> insert into sourcetable select object_name from user_objects
2 where rownum < 5;

4 rows created.

SQL>
SQL>
SQL> select index_name,status from user_indexes
2 where table_name = 'DESTTABLE'
3 /

INDEX_NAME STATUS
------------------------------ --------
DESTTABLE_IDX VALID

SQL>
SQL>
SQL> ALTER TABLE sourcetable EXCHANGE PARTITION p1 WITH TABLE
2 DESTTABLE
3 /

Table altered.

SQL>
SQL> select index_name,status from user_indexes
2 where table_name ='DESTTABLE'
3 /

INDEX_NAME STATUS
------------------------------ --------
DESTTABLE_IDX UNUSABLE

SQL>
SQL>


============================================================


Scenario II
============

Source table is non partitioned table and target is partitioned table.


SQL> drop table sourcetable;

Table dropped.

SQL> drop table desttable;

Table dropped.

SQL>
SQL> CREATE TABLE sourcetable
2 (OBJECT_NAME VARCHAR2(100))
3 /

Table created.

SQL>
SQL>
SQL> CREATE TABLE DESTTABLE
2 (OBJECT_NAME VARCHAR2(100))
3 PARTITION BY RANGE (OBJECT_NAME)
4 (PARTITION P1 VALUES LESS THAN (MAXVALUE))
5 /

Table created.

SQL>
SQL>
SQL> CREATE INDEX DESTTABLE_IDX ON desttable
2 (OBJECT_NAME) LOCAL
3 /

Index created.

SQL>
SQL> insert into sourcetable select object_name from user_objects
2 where rownum < 5;

4 rows created.

SQL>
SQL>
SQL> select index_name,status from user_indexes
2 where table_name ='DESTTABLE'
3 /

INDEX_NAME STATUS
------------------------------ --------
DESTTABLE_IDX N/A

SQL>
SQL>
SQL> ALTER TABLE desttable EXCHANGE PARTITION p1 WITH TABLE
2 sourcetable
3 /

Table altered.

SQL>
SQL> select index_name,status from user_indexes
2 where table_name ='DESTTABLE'
3 /

INDEX_NAME STATUS
------------------------------ --------
DESTTABLE_IDX N/A

SQL>

[pranavgovind]

Can any one help ?

[slimdave]

Your two scenarios are actually not much different from each other. you see N/A as the status for the local index on the partitioned table because there are no index segments at the global level -- the status is only meaningful at the partition level.

But you example of exchanging a partition when the table has only one index is a special case -- where you have multiple partitions and a global index you cannot preserve the indexing of the single partition when it is exchanged with a non-partitioned table because the global index contains references to all rows fof the partitioned table regardless of what partition they are in -- the rows are all "interleaved" in the same index structure.

So when you exchange a partition of a table with a global index you cannot expect the index information to be painlessly transferred with that data to the non-partitioned table.

Does that help?

[pranavgovind]

David, thanks. it helps.