1 Reply Latest reply: Dec 19, 2013 4:05 AM by Solomon Yakobson RSS

    index cant be local if we go for composite partition is it true?

    supersen

      Hi,

       

      I have created a partitioned table  TEMP as below

      create  table temp( id number, date1  date) partition by range (date1) subpartition by id store 8;

       

      Issue comes when i try to add primary key.  the above table  has more than 170 million records.

       

      Adding primary key takes me more than  4 hours. 

       

      So i decided to add primay key first with disable option and create unique index on it.

       

      ALTER TABLE "temp" ADD CONSTRAINT "ID_PK"

      PRIMARY KEY ("ID") DISABLE

      ---created in 1 seconds

      WHEN i try to add unique index for this

      CREATE UNIQUE INDEX id_pk ON temp

          (

      id

          ) 

      LOCAL PARALLEL 8 NOLOGGING TABLESPACE data

      i got error as below

       

      ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE

      index

       

       

      Please tell me  if we are going for composite partition as above index cant be created local?

       

      Please suggest

       

      Thanks

      Senthil.V

        • 1. Re: index cant be local if we go for composite partition is it true?
          Solomon Yakobson

          This is true for any unique index for any type of partitioning. What mean local index? It means each index partition is associated with a different table partition, so that all keys in an index partition refer only to rows stored in a single table partition. In such case the only way we can guarantee uniqueness across partitions (remember unique index means there is only one row with such index key, except NULL, in whole table) is having partitioning columns as part of index key.

           

          SY.