Forum Stats

  • 3,781,163 Users
  • 2,254,485 Discussions
  • 7,879,599 Comments

Discussions

Adding new partition causes invalid index ?

662384
662384 Member Posts: 7
edited Jan 4, 2009 5:27AM in General Database Discussions
Hi,

I' am on 10gr1.I have an huge table which is month partitoned on date column.For this new year i have to add new partitions.
So I wonder,Adding a new partition on a partitioned table causes invalid index.Must i rebuild index after this adding process.

Thanks in advice.
Tagged:

Answers

  • 492514
    492514 Member Posts: 348
    Yes, you have to rebuild the index (partitions). Repartitioning leads to records being stored in other objects (=table partitions), therefore indexes will become invalid.
    It certainly should be worth an Oracle enhancement request to include index rebuilding in partition changing operations.
  • OrionNet
    OrionNet Member Posts: 4,542
    Dante,


    Adding new partitions will not cause any nvalid indexes, even if you have have local as well global inexes on this partition table. Where dropping might be a different case.

    Regards
  • Surachart Opun
    Surachart Opun Member Posts: 1,662
    edited Dec 29, 2008 2:04PM
    When You add range partition tables with using global or local index , that can make indexes invalided, when old data that move.....

    You have to rebuild indexes.

    Edited by: HunterX (Surachart Opun) on Dec 30, 2008 2:02 AM
  • 492514
    492514 Member Posts: 348
    You should be cautious with such global statements :-)
  • OrionNet
    OrionNet Member Posts: 4,542
    Hello,

    That seems not right statement ; here is small test, added a partition for next year with out invalidating indexes.
    SELECT index_name, status
    FROM user_indexes
    WHERE table_name = 'MYTABLE';
    
    INDEX_NAME                     STATUS  
    ------------------------------ --------
    INDXA                          N/A     -- LOCAL INDEX
    INDX_GLOBAL                    VALID   -- Non-unique global index
    USBCINSTANTDECISION_PK         VALID   -- Primary Key
    
    
    5 rows selected.
    
    
    ALTER TABLE mytable
     ADD
      PARTITION mynewpartion VALUES LESS THAN (TO_DATE('2009-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
        LOGGING
        NOCOMPRESS;
    
    Table altered.
    
    
    SELECT index_name, status
    FROM user_indexes
    WHERE table_name = 'MYTABLE';
    
    
    INDEX_NAME                     STATUS  
    ------------------------------ --------
    INDXA                          N/A     
    INDX_GLOBAL                    VALID   
    USBCINSTANTDECISION_PK         VALID   
    OrionNet
  • 152941
    152941 Member Posts: 13
    Danteo1,

    If you are using Range partitioning, as a practice, always define a Future partition which will catch all exceptions. You can create new partitions by splitting the FUTURE partition (one with MAXVALUE). If the FUTURE partition is empty, indexes will be fine. You will have to rebuild index partions ONLY if the FUTURE partition has any data and got re-arranged during the split operation. Play with some of the options and you will hit scenarios and issues that you could never imagine.

    Regards,

    Shaji.
    152941
  • Surachart Opun
    Surachart Opun Member Posts: 1,662
    edited Dec 29, 2008 2:27PM
    I'm sorry about my confused.


    I added hash/etc... partition table with global index / local index => invalided ...

    when I added partition without option

    UPDATE GLOBAL INDEXES or UPDATE INDEXES


    CREATE TABLE MYTABLE (
    id NUMBER(5) NOT NULL,
    last_name VARCHAR2(30),
    dob DATE)
    PARTITION BY RANGE (dob)
    (PARTITION p1 VALUES LESS THAN (TO_DATE('2008-01-01','YYYY-MM-DD')),
    partition pmax VALUES LESS THAN (MAXVALUE)
    );



    CREATE INDEX MYINDEX_GLOBAL ON MYTABLE (dob)
    GLOBAL PARTITION BY RANGE (dob)
    (PARTITION p1 VALUES LESS THAN (TO_DATE('2008-01-01','YYYY-MM-DD')),
    partition pmax VALUES LESS THAN (MAXVALUE)
    );


    begin
    for x in 1 .. 10000
    loop
    insert into MYTABLE values (1,'TEST',sysdate + x);
    end loop;
    end;
    /
    commit;

    select index_name, partition_name, status, num_rows from user_ind_partitions where index_name='MYINDEX_GLOBAL';

    INDEX_NAME PARTITION_NAME STATUS NUM_ROWS
    ------------------------------------------------------------------------------
    MYINDEX_GLOBAL P1 USABLE
    MYINDEX_GLOBAL PMAX USABLE


    analyze INDEX MYINDEX_Global compute statistics;

    select index_name, partition_name, status, num_rows from user_ind_partitions where index_name='MYINDEX_GLOBAL';

    INDEX_NAME PARTITION_NAME STATUS NUM_ROWS
    ------------------------------------------------------------------------------
    MYINDEX_GLOBAL P1 USABLE 0
    MYINDEX_GLOBAL PMAX USABLE 10000

    ALTER TABLE mytable SPLIT PARTITION pmax AT (TO_DATE('2010-01-01','YYYY-MM-DD')) INTO (PARTITION mynewpartion, PARTITION pmax) ;

    select index_name, partition_name, status, num_rows from user_ind_partitions where index_name='MYINDEX_GLOBAL';

    INDEX_NAME PARTITION_NAME STATUS NUM_ROWS
    ------------------------------------------------------------------------------
    MYINDEX_GLOBAL P1 UNUSABLE 0
    MYINDEX_GLOBAL PMAX UNUSABLE 10000

    Anyway use "UPDATE INDEXES"

    ALTER TABLE mytable SPLIT PARTITION pmax AT (TO_DATE('2010-01-01','YYYY-MM-DD')) INTO (PARTITION mynewpartion, PARTITION pmax) UPDATE INDEXES;

    select index_name, partition_name, status, num_rows from user_ind_partitions where index_name='MYINDEX_GLOBAL';

    INDEX_NAME PARTITION_NAME STATUS NUM_ROWS
    ------------------------------------------------------------------------------
    MYINDEX_GLOBAL P1 USABLE 0
    MYINDEX_GLOBAL PMAX USABLE 10000

    OR

    ALTER TABLE mytable SPLIT PARTITION pmax AT (TO_DATE('2010-01-01','YYYY-MM-DD')) INTO (PARTITION mynewpartion, PARTITION pmax) UPDATE GLOBAL INDEXES ;

    select index_name, partition_name, status, num_rows from user_ind_partitions where index_name='MYINDEX_GLOBAL';

    INDEX_NAME PARTITION_NAME STATUS NUM_ROWS
    ------------------------------------------------------------------------------
    MYINDEX_GLOBAL P1 USABLE 0
    MYINDEX_GLOBAL PMAX USABLE 10000


    Edited by: HunterX (Surachart Opun) on Dec 30, 2008 2:16 AM
  • 662384
    662384 Member Posts: 7
    Hi,

    We are using range partitioning.So after adding new null partitions , all indexes remained usable.
    Thanks for all responses.

    Regards.
This discussion has been closed.