Split partition Optimization

Learnt something new in Oracle its about Split partition optimization.

If all rows after split end up to a single partition and if you meet the pre-requisites in the above link then the Split partition command is similar to Add Partition. that is Split partition command doesn't make row-movements.

1) if No row movements then No row-id changes.
2) if No row-id changes then your global indexes remains VALID without "update indexes" or "update global index" having mentioned in your partition maintenance operations.

Table 'T' created with couple of rows with all rows available in partition 'P3'

rajesh@ORA11G> create table t
  2  ( x int,
  3    y varchar2(30),
  4    z date )
  5  partition by range(z)
  6  ( partition p1 values less than ( to_date('15-feb-2012','dd-mon-yyyy') ),
  7    partition p2 values less than ( to_date('17-feb-2012','dd-mon-yyyy') ),
  8    partition p3 values less than ( to_date('20-apr-2012','dd-mon-yyyy') ) ) ;

Table created.
rajesh@ORA11G>
rajesh@ORA11G> insert into t values( 1,'a', to_date('25-feb-2012','dd-mon-yyyy') );

1 row created.
rajesh@ORA11G> insert into t values( 2,'b', to_date('26-feb-2012','dd-mon-yyyy') );

1 row created.
rajesh@ORA11G> commit;

Commit complete.
rajesh@ORA11G>
rajesh@ORA11G> select * from t partition(p3);
         X Y                              Z
---------- ------------------------------ -----------------------
         1 a                              25-FEB-2012 12:00:00 AM
         2 b                              26-FEB-2012 12:00:00 AM

2 rows selected.
rajesh@ORA11G> create table tmp as
  2  select rowid rid,z
  3  from t ;

Table created.
rajesh@ORA11G> create index t_ind on t(x);

Index created.
rajesh@ORA11G> select index_name, status
  2  from user_indexes
  3  where index_name ='T_IND';

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          VALID

1 row selected.
rajesh@ORA11G>

Now after splitting all rows are available in partition 'P201'  without any row movements (no changes in rowid) but still global index remains VALID without having "UPDATE INDEXES" mentioned in split partition ddl command.

rajesh@ORA11G> alter table t
  2  split partition p3
  3  at ( to_date('01-mar-2012','dd-mon-yyyy') )
  4  into ( partition p201, partition p3 ) ;

Table altered.
rajesh@ORA11G> select * from t partition(p201);
         X Y                              Z
---------- ------------------------------ -----------------------
         1 a                              25-FEB-2012 12:00:00 AM
         2 b                              26-FEB-2012 12:00:00 AM

2 rows selected.
rajesh@ORA11G> select rid, t.z, t.rowid
  2  from t , tmp
  3  where t.z = tmp.z
  4  and t.rowid <> tmp.rid ;

no rows selected
rajesh@ORA11G>
rajesh@ORA11G> select index_name, status
  2  from user_indexes
  3  where index_name ='T_IND';
INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          VALID

1 row selected.
rajesh@ORA11G>


If the partition being split is has different storage characteristic from the other partition, (or) if all rows are not moved after split then this Optimization will never kicks off.

Now table got created in logging mode, with couple of rows in P3 along with Global index.

rajesh@ORA11G> create table t
  2  ( x int,
  3    y varchar2(30),
  4    z date ) logging
  5  partition by range(z)
  6  ( partition p1 values less than ( to_date('15-feb-2012','dd-mon-yyyy') ),
  7    partition p2 values less than ( to_date('17-feb-2012','dd-mon-yyyy') ),
  8    partition p3 values less than ( to_date('20-apr-2012','dd-mon-yyyy') ) ) ;

Table created.
rajesh@ORA11G>
rajesh@ORA11G> insert into t values( 1,'a', to_date('25-feb-2012','dd-mon-yyyy') );

1 row created.
rajesh@ORA11G> insert into t values( 2,'b', to_date('26-feb-2012','dd-mon-yyyy') );

1 row created.
rajesh@ORA11G> commit;

Commit complete.
rajesh@ORA11G>
rajesh@ORA11G> create table tmp as
  2  select rowid rid,z
  3  from t ;

Table created.
rajesh@ORA11G> create index t_ind on t(x);

Index created.
rajesh@ORA11G>
rajesh@ORA11G> select index_name, status
  2  from user_indexes
  3  where index_name ='T_IND';

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          VALID

1 row selected.

partition p3 got split with new partition p201 in nologging mode (which got different characteristics from the partition p3) due to this Optimization doesn't kicks off and cause row-movements making global index un-usable.

rajesh@ORA11G> alter table t
  2  split partition p3
  3  at ( to_date('01-mar-2012','dd-mon-yyyy') )
  4  into ( partition p201 nologging , partition p3 ) ;

Table altered.
rajesh@ORA11G>
rajesh@ORA11G> select rid, t.z, t.rowid
  2  from t , tmp
  3  where t.z = tmp.z
  4  and t.rowid <> tmp.rid ;

RID                Z                       ROWID
------------------ ----------------------- ------------------
AAAU32AAFAAAACkAAA 25-FEB-2012 12:00:00 AM AAAU35AAFAAAADLAAA
AAAU32AAFAAAACkAAB 26-FEB-2012 12:00:00 AM AAAU35AAFAAAADLAAB

2 rows selected.
rajesh@ORA11G>
rajesh@ORA11G> select index_name, status
  2  from user_indexes
  3  where index_name ='T_IND';

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          UNUSABLE

1 row selected.
rajesh@ORA11G>