Placement of new local index segments on interval partitoned table
Date: Sat, 1 Aug 2009 17:26:49 +0100
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
( 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,
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 .....
PeteReceived on Sat Aug 01 2009 - 11:26:49 CDT