4 Replies Latest reply: Jan 2, 2013 10:54 AM by rp0428 RSS

    interval partitioning indexes

    187007
      Hi,

      I am unable to get the indexes interval partitioned, it always throws "ORA-00906: missing left parenthesis"

      Example:
      -- interval partitioning the table works fine as below
      create table t1 (partitionID number, name varchar2(200))
      partition by range (partitionID) interval (1)
      (partition p1 values less than (2))

      -- Now, lets try building a global interval partitioned index on this
      -- Though it is possible to simply tag it as local, I would like to create it as global for other reasons

      create unique index t1_u1 on t1(partitionID) global
      partition by range (partitionID) interval (1)
      (partition p1 values less than (2))

      Index creation as above fails for me with "ORA-00906: missing left parenthesis" and I can't figure out why it is doing so.

      I tried to see if I can range partition the index and add interval later, but that fails too
      SQL> create unique index t1_u1 on t1(partitionID) global
      2 partition by range (partitionID)
      3 (partition p1 values less than (MAXVALUE));

      Index created.

      SQL> alter index t1_u1 set interval (1);
      alter index t1_u1 set interval (1)
      *
      ERROR at line 1:
      ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

      Isn't interval partitioning not supported for indexes ?