04 September, 2011

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.

.
.
.

3 comments:

Prashanth said...

Hi Hemant,
Hope all is well.
I am Prashanth K, working as Oracle DBA Trainer.
I came across your blog. Its nice.
I need small favour from you.
I need to clarify some doubts in Oracle DBA.
I want to know Old SCN no.of particular table that is created. So from where I can view it.
I would be very grateful, if you could give this small piece information.

Hemant K Chitale said...

Prashanth,
I do not know what you mean by "Old SCN no.of particular table that is created."
You can get the creation time from USER_OBJECTS / DBA_OBJECTS.
Once you know the time, you can get an *approximation* of the SCN with
"select timestamp_to_scn(to_date('16-SEP-11 23:25:00','DD-MON-RR HH24:MI:SS')) from dual;"

Note that the functions timestamp_to_scn and scn_to_timestamp return *approximations* not exact values. Furthermore, the "history" of SCN-Timestamp mappings is limited.

See http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions176.htm

Prashanth said...

Hi Hemant,
Thanks for the response.
The link was very helpful
I appreciate your timely help

Thank you
Prashanth K