6 Replies Latest reply: Jul 4, 2012 11:53 AM by rp0428 RSS

    Any maintenance in partition table cause PK index unusable state

    771073
      Hi Guys-

      I am facing a problem while trying to maintenance partitioned table......each time i run alter table statement to Drop/Truncate partition it causes the primary key index to be in "unusable state" and it really take a lot of time for the re-building process of the primary key


      Could you please advice?


      Regards,
      Maylo
        • 1. Re: Any maintenance in partition table cause PK index unusable state
          sb92075
          user1110976 wrote:
          Hi Guys-

          I am facing a problem while trying to maintenance partitioned table......each time i run alter table statement to Drop/Truncate partition it causes the primary key index to be in "unusable state" and it really take a lot of time for the re-building process of the primary key


          Could you please advice?


          Regards,
          Maylo
          is PK index GLOBAL?

          post DDL for the PK INDEX
          • 2. Re: Any maintenance in partition table cause PK index unusable state
            Solomon Yakobson
            Which tells me PK is a global index. Use Drop/Truncate partition with UPDATE GLOBAL INDEXES clause. For example:
            SQL> alter table range_sales drop partition SALES_Q4_1998 update global indexes;
            
            Table altered.
            
            SQL> 
            SY.
            • 3. Re: Any maintenance in partition table cause PK index unusable state
              771073
              _1) Here is the script of the table_

              CREATE TABLE "MY_TABLE"
              (     "COLUMN_01" NUMBER(20,0),
                   "COLUMN_02" NUMBER(12,0),
              "COLUMN_03" VARCHAR2(20),
                   "VAR1" VARCHAR2(1)
              ) PARTITION BY LIST (COLUMN_03)
              SUBPARTITION BY HASH(COLUMN_01,COLUMN_02)
              SUBPARTITION TEMPLATE(
              SUBPARTITION sp1 ,
              SUBPARTITION sp2 ,
              SUBPARTITION SP3 ,
              SUBPARTITION sp4 )
              (      
              PARTITION SOURCE_FP VALUES ('VALUE01'),
              partition SOURCE_VARIANT_DB values ('VALUE02'));


              ALTER TABLE "MY_TABLE"
              ADD CONSTRAINT "PK_INF_SNP" PRIMARY KEY ("COLUMN_03","COLUMN_01", "COLUMN_02")
              USING INDEX (CREATE UNIQUE INDEX X_INX ON MY_TABLE("COLUMN_03","COLUMN_01", "COLUMN_02") LOCAL );


              2) Then i fill the main partitions (SOURCE_FP & SOURCE_VARIANT_DB) with Data

              3) Then i run the following script to Truncate the partition SOURCE_FP
              ALTER TABLE MY_TABLE TRUNCATE PARTITION SOURCE_FP;

              4) After that whenever i tried to insert more data ...the following error is shown


              INSERT INTO MY_TABLE (LIB_GENOME_PAIR_ID,POS,DATA_SOURCE,VAR1,TYP,IS_REFERENCE)
              VALUES ('612675547','3465260','FP_MRT','C');


              Error report:
              SQL Error: ORA-01502: index 'Schema_name.PK_INF_SNP' or partition of such index is in unusable state
              +01502. 00000 - "index '%s.%s' or partition of such index is in unusable state"+
              *Cause:    An attempt has been made to access an index or index partition+
              that has been marked unusable by a direct load or by a DDL
              operation
              *Action:   DROP the specified index, or REBUILD the specified index, or+
              REBUILD the unusable index partition


              Hoping you could provide any further help in that urgent issue

              Kind Regards,
              Maylo
              • 4. Re: Any maintenance in partition table cause PK index unusable state
                sb92075
                >

                do as below
                ALTER TABLE MY_TABLE TRUNCATE PARTITION SOURCE_FP update global indexes;
                • 5. Re: Any maintenance in partition table cause PK index unusable state
                  Hemant K Chitale
                  The Index is Equ-Partitioned with the table-- so it should be SubPartitioned. Are you sure that you have the correct definition ?
                  SQL> drop TABLE MY_TABLE purge;
                  
                  Table dropped.
                  
                  SQL> 
                  SQL> CREATE TABLE MY_TABLE
                    2  ( COLUMN_01 NUMBER(20,0),
                    3  COLUMN_02 NUMBER(12,0),
                    4  COLUMN_03 VARCHAR2(20),
                    5  VAR1 VARCHAR2(1)
                    6  ) PARTITION BY LIST (COLUMN_03)
                    7  SUBPARTITION BY HASH(COLUMN_01,COLUMN_02)
                    8  SUBPARTITION TEMPLATE(
                    9  SUBPARTITION sp1 ,
                   10  SUBPARTITION sp2 ,
                   11  SUBPARTITION SP3 ,
                   12  SUBPARTITION sp4 )
                   13  (
                   14  PARTITION SOURCE_FP VALUES ('VALUE01'),
                   15  partition SOURCE_VARIANT_DB values ('VALUE02'));
                  
                  Table created.
                  
                  SQL> 
                  SQL> ALTER TABLE MY_TABLE
                    2  ADD CONSTRAINT PK_INF_SNP PRIMARY KEY (COLUMN_03,COLUMN_01, COLUMN_02)
                    3  USING INDEX (CREATE UNIQUE INDEX X_INX ON MY_TABLE(COLUMN_03,COLUMN_01, COLUMN_02) LOCAL );
                  
                  Table altered.
                  
                  SQL> 
                  SQL> 
                  SQL> insert into MY_TABLE select rownum, rownum, 'VALUE01', 'X'
                    2  from dual
                    3  connect by level < 10;
                  
                  9 rows created.
                  
                  SQL> commit;
                  
                  Commit complete.
                  
                  SQL> select count(*) from my_table;
                  
                    COUNT(*)
                  ----------
                           9
                  
                  SQL> 
                  SQL> alter table MY_TABLE truncate partition SOURCE_FP;
                  
                  Table truncated.
                  
                  SQL> 
                  SQL> select index_name, partition_name, subpartition_name, status from user_ind_subpartitions;
                  
                  INDEX_NAME   PARTITION_NAME    SUBPARTITION_NAME              STATUS
                  ------------ ----------------- ------------------------------ --------
                  X_INX        SOURCE_FP         SOURCE_FP_SP1                  USABLE
                  X_INX        SOURCE_FP         SOURCE_FP_SP2                  USABLE
                  X_INX        SOURCE_FP         SOURCE_FP_SP3                  USABLE
                  X_INX        SOURCE_FP         SOURCE_FP_SP4                  USABLE
                  X_INX        SOURCE_VARIANT_DB SOURCE_VARIANT_DB_SP1          USABLE
                  X_INX        SOURCE_VARIANT_DB SOURCE_VARIANT_DB_SP2          USABLE
                  X_INX        SOURCE_VARIANT_DB SOURCE_VARIANT_DB_SP3          USABLE
                  X_INX        SOURCE_VARIANT_DB SOURCE_VARIANT_DB_SP4          USABLE
                  
                  8 rows selected.
                  
                  SQL> 
                  SQL> insert into my_table values (1,1,'VALUE01','X');
                  
                  1 row created.
                  
                  SQL> commit;
                  
                  Commit complete.
                  
                  SQL> 
                  Hemant K Chitale
                  • 6. Re: Any maintenance in partition table cause PK index unusable state
                    rp0428
                    >
                    CREATE TABLE "MY_TABLE"
                    ( "COLUMN_01" NUMBER(20,0),
                    "COLUMN_02" NUMBER(12,0),
                    "COLUMN_03" VARCHAR2(20),
                    "VAR1" VARCHAR2(1)
                    )
                    . . .
                    INSERT INTO MY_TABLE (LIB_GENOME_PAIR_ID,POS,DATA_SOURCE,VAR1,TYP,IS_REFERENCE)
                    VALUES ('612675547','3465260','FP_MRT','C');
                    >
                    You can't insert into columns that don't exist. The columns in your INSERT statement do not exist in the table with the DDL you posted.

                    If you want help you need to provide the actuall DDL and DML statements that are causing the problem so we can try to reproduce and fix the problem.
                    The DML you posted is NOT for the table whose DDL you posted.