SPLIT PARTITION, reference partitioning and index support

The prellty well “silent” behaviour of SPLIT PARTITION operation is that Oracle extends index support option onto child tables SPLIT operations:

SQL> CREATE TABLE t_p
  2  (
  3      X INT CONSTRAINT T_P_PK PRIMARY KEY,
  4      Y INT NOT NULL
  5  )        
  6  PARTITION BY RANGE(Y) (
  7      PARTITION P_D VALUES LESS THAN (MAXVALUE)
  8  )
  9  /

Table created.

SQL> 
SQL> INSERT INTO T_P 
  2  SELECT rownum, MOD(rownum,3) 
  3  FROM dual
  4  CONNECT BY LEVEL  
SQL> CREATE TABLE t_c
  2  (
  3      X INT NOT NULL,
  4      Y INT NOT NULL,
  5      CONSTRAINT T_P_FK FOREIGN KEY(X)
  6      REFERENCES T_P(X)
  7  )        
  8  PARTITION BY REFERENCE(T_P_FK)
  9  /

Table created.

SQL> 
SQL> CREATE INDEX t_c_i ON t_c(x)
  2  /

Index created.

SQL> 
SQL> INSERT INTO T_C 
  2  SELECT rownum, MOD(rownum,3) 
  3  FROM dual
  4  CONNECT BY LEVEL  
SQL> COMMIT
  2  /

Commit complete.

SQL> 
SQL> REM Spoil index
SQL> ALTER TABLE T_P SPLIT PARTITION P_D
  2  AT(1) INTO (PARTITION P_1, PARTITION P_D)
  3  /

Table altered.

SQL> select status from user_indexes
  2  where table_name in ('T_P','T_C');

STATUS
--------
UNUSABLE
UNUSABLE

SQL> alter index t_p_pk rebuild;

Index altered.

SQL> alter index t_c_i rebuild;

Index altered.

SQL> select status, index_name, table_name
  2  from user_indexes
  3  where table_name in ('T_P','T_C');

STATUS   INDEX_NAME                     TABLE_NAME
-------- ------------------------------ ----------
VALID    T_C_I                          T_C
VALID    T_P_PK                         T_P

SQL> ALTER TABLE T_P SPLIT PARTITION P_D
  2  AT(2) INTO (PARTITION P_2, PARTITION P_D) UPDATE INDEXES
  3  /

Table altered.

SQL> select status, index_name, table_name
  2  from user_indexes
  3  where table_name in ('T_P','T_C');

STATUS   INDEX_NAME                     TABLE_NAME
-------- ------------------------------ ------------------------------
VALID    T_C_I                          T_C
VALID    T_P_PK                         T_P

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s