Splitting a Range Partitioned Table

Here's a simple demonstration of splitting a Range Partitioned Table :

SQL> --- Create a Table with a MAXVALUE partition

SQL> drop table sales purge;



Table dropped.



SQL> create table sales

  2  (order_id    number not null,

  3   sale_date   date,

  4   cust_id     number,

  5   prod_id     number,

  6   remarks     varchar2(128))

  7  partition by range (sale_date)

  8  (partition P_2007 values less than (to_date('01-JAN-2008')),

  9  partition P_2008 values less than (to_date('01-JAN-2009')),

 10  partition P_2009 values less than (to_date('01-JAN-2010')),

 11  partition P_2010 values less than (to_date('01-JAN-2011')),

 12  partition P_MAX  values less than (MAXVALUE)

 13  )

 14  /



Table created.



SQL> 

SQL> -- insert sales for years 2007 to 2011

SQL> insert into sales values (1,to_date('15-JAN-2007','DD-MON-YYYY'),1,1,'First Sale in 2007');



1 row created.



SQL> insert into sales values (2,to_date('15-JUL-2008','DD-MON-YYYY'),1,2,'Sale in 2008');



1 row created.



SQL> insert into sales values (3,to_date('15-JUL-2009','DD-MON-YYYY'),1,3,'Sale in 2009');



1 row created.



SQL> insert into sales values (4,to_date('15-JUL-2010','DD-MON-YYYY'),1,2,'Sale in 2010');



1 row created.



SQL> insert into sales values (5,to_date('15-AUG-2010','DD-MON-YYYY'),2,3,'Cust_2 in 2010');



1 row created.



SQL> insert into sales values (6,to_date('01-FEB-2011','DD-MON-YYYY'),1,2,'Feb 2011');



1 row created.



SQL> insert into sales values (7,to_date('01-MAR-2011','DD-MON-YYYY'),2,3,'Mar 2011');



1 row created.



SQL> insert into sales values (8,to_date('01-APR-2011','DD-MON-YYYY'),2,3,'Apr 2011');



1 row created.



SQL> insert into sales values (9,to_date('01-MAY-2011','DD-MON-YYYY'),3,3,'Cust_3 !');



1 row created.



SQL> commit;



Commit complete.



SQL> 

SQL> -- create a global index and a local index

SQL> create unique index S_ORDER_ID_NDX on SALES (ORDER_ID);



Index created.



SQL> create index S_SALE_DT_NDX on SALES (SALE_DATE) LOCAL ;



Index created.



SQL> 

SQL> -- gather stats

SQL> exec dbms_stats.gather_table_stats('','SALES',estimate_percent=>100,granularity=>'ALL');



PL/SQL procedure successfully completed.



SQL> select partition_name, num_rows

  2  from user_tab_partitions

  3  where table_name = 'SALES'

  4  order by partition_position

  5  /



PARTITION_NAME                   NUM_ROWS

------------------------------ ----------

P_2007                                  1

P_2008                                  1

P_2009                                  1

P_2010                                  2

P_MAX                                   4



SQL> 

SQL> -- We now find that year 2011 sales went into the Max value partition !

SQL> 

SQL> -- split Max value partition

SQL> alter table sales

  2  split partition P_MAX

  3  at (to_date('01-JAN-2012','DD-MON-YYYY'))

  4  into (partition P_2011,partition P_MAX)

  5  update global indexes

  6  /



Table altered.



SQL> alter table sales

  2  split partition P_MAX

  3  at (to_date('01-JAN-2013','DD-MON-YYYY'))

  4  into (partition P_2012,partition P_MAX)

  5  update global indexes

  6  /



Table altered.



SQL> alter table sales

  2  split partition P_MAX

  3  at (to_date('01-JAN-2014','DD-MON-YYYY'))

  4  into (partition P_2013,partition P_MAX)

  5  update global indexes

  6  /



Table altered.



SQL> 

SQL> -- check the indexes

SQL> select status from user_indexes where index_name = 'S_ORDER_ID_NDX';



STATUS

--------

VALID



SQL> 

SQL> select partition_name, status

  2  from user_ind_partitions

  3  where index_name = 'S_SALE_DT_NDX'

  4  order by partition_position

  5  /



PARTITION_NAME                 STATUS

------------------------------ --------

P_2007                         USABLE

P_2008                         USABLE

P_2009                         USABLE

P_2010                         USABLE

P_2011                         USABLE

P_2012                         USABLE

P_2013                         USABLE

P_MAX                          USABLE



8 rows selected.



SQL> 

SQL> -- The LOCAL index (equi-partitioned) is automatically maintained

SQL> -- with even new partitions created

SQL> 

SQL> -- gather stats

SQL> exec dbms_stats.gather_table_stats('','SALES',estimate_percent=>100,granularity=>'ALL');



PL/SQL procedure successfully completed.



SQL> select partition_name, num_rows

  2  from user_tab_partitions

  3  where table_name = 'SALES'

  4  order by partition_position

  5  /



PARTITION_NAME                   NUM_ROWS

------------------------------ ----------

P_2007                                  1

P_2008                                  1

P_2009                                  1

P_2010                                  2

P_2011                                  4

P_2012                                  0

P_2013                                  0

P_MAX                                   0



8 rows selected.



SQL> 

The time to execute the SPLIT commands depends on the number of rows present in the P_MAX partition.

.
.
.