Thursday, January 16, 2014

How to drop a table partition without invalidating global indexes

My interval range partitioned table looks as follows:

CREATE TABLE SEGMENT_SIZES(
  SEGMENT_SIZES_ID NUMBER,
  STIMESTAMP      DATE,
  OWNER           VARCHAR2(30 BYTE),
  SEGMENT_NAME    VARCHAR2(30 BYTE),
  PARTITION_NAME  VARCHAR2(30 BYTE),
  SEGMENT_TYPE    VARCHAR2(20 BYTE),
  BYTES           NUMBER,
  CONSTRAINT SEGMENT_SIZES_PK PRIMARY KEY (SEGMENT_SIZES_ID)
)
PARTITION BY RANGE (STIMESTAMP )
-- Use 11gR1 Interval Partitioning
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION P062013 VALUES LESS THAN (TO_DATE('01.07.2013','DD.MM.YYYY'))
)
TABLESPACE TOOLS
ENABLE ROW MOVEMENT
COMPRESS FOR ALL OPERATIONS;
It was populated with data, which created the needed partititions automatically.
The primary key defined on the table will of course create a global index spanning all partitions.
Consequently, during partition maintenance operations, you will end up with an UNUSUABLE primary key index if you drop a partition, as follows:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42;

However, if you you add the "UPDATE INDEXES" clause, oracle will update the global index; the index will remain USABLE:

ALTER TABLE SEGMENT_SIZES DROP PARTITION SYS_P42 UPDATE INDEXES;

Keep in mind that you cannot drop the highest range partition of an interval-partitioned table!

Source: Oracle Documentation

No comments:

Post a Comment