This discussion is archived
4 Replies Latest reply: Jan 2, 2013 8:54 AM by rp0428 RSS

interval partitioning indexes

187007 Newbie
Currently Being Moderated
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 ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points