Search My Oracle Blog

Custom Search

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

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016