Placement of new local index segments on interval partitoned table

From: Peter Hitchman <pjhoraclel_at_gmail.com>
Date: Sat, 1 Aug 2009 17:26:49 +0100
Message-ID: <5e317f310908010926pb329603pb7ec504a36acfb84_at_mail.gmail.com>



Hi,
Oracle 11.1.0.6
Platform: Linix OEL V4 64 bit

The issues around this have bugged me for some time.

If I create an interval partitioned table like this:

CREATE TABLE TEST_INTERVAL
(
 MY_KEY NUMBER NOT NULL
,PART_KEY DATE NOT NULL
)

PARTITION BY RANGE (PART_KEY) INTERVAL (NUMTOYMINTERVAL(1,'YEAR')) STORE IN (CSGDATA,DPS_SUPPORT)
(
 PARTITION PT_1 VALUES LESS THAN ('01-JAN-2008') TABLESPACE CSGDATA
)

/

and the put a PK contraint on it with a local index:

ALTER TABLE TEST_INTERVAL
ADD CONSTRAINT TI_PK PRIMARY KEY (PART_KEY) -- (MY_KEY) ERROR at line 1:

  • ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index USING INDEX LOCAL ( PARTITION TI_PK01 TABLESPACE DPS_SUPPORT -- Can place first partition
    )
    /

when I create new table segments then the new index segments that get created go in the same tablespace as the table segment.

So once I realise what's happend (having received some tablespace filling up messages) I rebuild the indexes to the tablespaces I want.

Does it have to be this way?

I found that if I added a tablespace instruction to the end of the "ADD CONSTRAINT" DDL, then all of the new index segments go to that tablespace. Reasonable as this is the default tablespace for the index.

What I really want to be able to use is the "STORE IN" syntax with the index for the PK constraint, but that just throws an error: "ORA-00907: missing right parenthesis".

I tried something like this:
CREATE UNIQUE INDEX TRA_PK ON TEST_INTERVAL (PART_KEY) LOCAL ( PARTITION TT1_PK (

  SUBPARTITION A,
  SUBPARTITION B,
  SUBPARTITION C,
  SUBPARTITION D,
  SUBPARTITION E,
  SUBPARTITION F,
  SUBPARTITION G,

  SUBPARTITION H
  )
  STORE IN (TS_A,TS_B,TS_C,TS_D))
/

I just thought I have been missing something here. It seems odd to me that Oracle provides the ability to automate the creation of table partitions and to round robin the segment placement of the table segments but not for indexes created on the table.

So if anyone has any pointers .....

Thanks

Pete

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Aug 01 2009 - 11:26:49 CDT

Original text of this message