Index rebuild on exchange partition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Index rebuild on exchange partition

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    Index rebuild on exchange partition

    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

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    is it on Oracle 8.0.3 or 8.1.5 (8i) ?

  3. #3
    Join Date
    Jun 2006
    Posts
    259
    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.

  4. #4
    Join Date
    Dec 2005
    Posts
    195
    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>

  5. #5
    Join Date
    Dec 2005
    Posts
    195
    Can any one help ?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2005
    Posts
    195
    David, thanks. it helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width