Thursday, March 29, 2012

How to Partition an Existing Table

This example was tested on Oracle 9. First create an ordinary table with some data in and count the number of rows it contains:

SQL> create table andrew
  2  as select * from dba_tables
  3  /

Table created.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
       831

SQL>

Next create an empty partitioned version of the table and show that it is empty:

SQL> create table andrew_partitioned
  2  partition by range(owner)
  3  (partition part01
  4  values less than (maxvalue))
  5  as select * from andrew
  6  where 1=2
  7  /

Table created.

SQL> select count(*) from andrew_partitioned
  2  /

  COUNT(*)
----------
         0

SQL>

Now move the data from the ordinary table to the partitioned table:

SQL> alter table andrew_partitioned
  2  exchange partition part01
  3  with table andrew
  4  /

Table altered.

SQL>

Show that the ordinary table is empty and that the partitioned table now has all the data:

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
         0

SQL> select count(*) from andrew_partitioned
  2  /

  COUNT(*)
----------
       831

SQL>

Give the partitioned table the same name as the original non-partitioned one:

SQL> drop table andrew
  2  /

Table dropped.

SQL> rename andrew_partitioned to andrew
  2  /

Table renamed.

SQL> 

Finally, you can split up the new partitioned table as you wish:

SQL> alter table andrew
  2  split partition part01 at ('O')
  3  into (partition part01, partition part02)
  4  /

Table altered.

SQL>

No comments: