1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Restriction to make primary or unique key partitioned local index

Discussion in 'Other Development Tools' started by Arju, Nov 6, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Below is the detailed example. The rule is if in the table a column is included as a primary or unique key and the column is not included in the partitioned key, then on that column we will not be able to make any local partitioned index.

    However if the columns is not included in primary or unique key columns then we may wish to make any local partitioned index whether they are included in the partitioned key.

    Making on local partitioned index on column a will fail as a is unique key and partition is not on column a.
    Code (Text):
    SQL>  create table test_U(a number ,
      2  b number ,
      3  constraint a_uk unique(a) using  index local)
      4  partition by range(b) (partition p1 values less than (10), partition p2 values less than
      5   (maxvalue));
     create table test_U(a number ,
    *
    ERROR at line 1:
    ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
    index
     
    Making a local paritioned index on column b will allow as b is included partitioned.

    Code (Text):
    SQL> create table test_U(a number ,
      2  b number ,
      3  constraint b_uk unique(b) using  index local)
      4  partition by range(b) (partition p1 values less than (10), partition p2 values less than
      5   (maxvalue));

    Table created.
    Code (Text):
    SQL> drop table test_u;

    Table dropped.
    Looking for other possibility,
    Code (Text):
    SQL> create table test_U(a number ,
      2  b number ,
      3  constraint b_uk unique(a,b) using  index local)
      4  partition by range(b) (partition p1 values less than (10), partition p2 values less than
      5   (maxvalue));

    Table created.
    Code (Text):
    SQL> drop table test_u;

    Table dropped.
    Code (Text):
    SQL> create table test_U(a number , b number )
      2  partition by range(b) (partition p1 values less than (10), partition p2 values less than
      3   (maxvalue));

    Table created.