Martins Blog

Trying to explain complex things in simple terms

Introduce Partitioning to a large table

Posted by Martin Bach on September 5, 2009

Recently I have been asked to find a way to partition a very big table on very slow storage. Very large table translates into 600G, and slow storage translates into HP MSA 1000. The time window wasn’t big enough to run this, and the insert activity on the table required it to be done on less busy times.

DBMS_REDEFINITION wasn’t an option then so I decided on a multi-step approach. If I can’t neatly use dbms_redefinition then at least I needed the power of the partitioning commands. Here’s the proof of concept.

WARNING
As alwyas, don’t simply copy and paste – this works for me but doesn’t mean it also works for you. Test, test, test first before doing this in production.

Create a “large” table as source first to test. Actually it’s not large at all but that’s besides the point…

create table unpart
tablespace users
as select * from dba_objects;

Table created.

09:53:05 SQL> select count(1) from unpart;

  COUNT(1)
----------
      9960

Now let’s put some more data into it:

begin
 for i  in 1..5 loop  
  insert into unpart select * from unpart;
 end loop;
end;
/

09:53:26 SQL> /

PL/SQL procedure successfully completed.

09:53:27 SQL> commit;

Commit complete.

09:53:30 SQL> select count(1) from unpart;

  COUNT(1)
----------
    318720

Slightly better. I now need to find the upper bound for the newly to be created partition. This is where you shouldn’t insert data into the table…

SQL> select max(object_id) from unpart;

MAX(OBJECT_ID)
--------------
         10459

Now create the partitioned table to hold the data after the move. The whole source table should fit into the first partition.

create table part
partition by range (object_id)
(
 partition p1 values less than (10459),
 partition p2 values less than (20000),
 partition p3 values less than (maxvalue)
)
as select * from unpart where 1 = 0;

SQL> alter table part enable row movement;

Table altered.

A few selects to prove that the table is empty:

SQL> select count(1) from part partition (p1);

  COUNT(1)
----------
         0

SQL> select count(1) from part partition (p2)
  2  /

  COUNT(1)
----------
         0

SQL> select count(1) from part partition (p3);

  COUNT(1)
----------
         0

Now let’s exchange the table with the initial partition:

SQL> alter table part exchange partition p1 with table unpart;

Table altered.

SQL> select count(1) from part partition (p1)
  2  /

  COUNT(1)
----------
    318688

SQL> select count(1) from part partition (p2);

  COUNT(1)
----------
         0

-- Now we can partition the rest!

09:56:22 SQL> alter table part split partition p1 at (5000) into (partition p_del, partition p1) 
           2  update global indexes parallel 4;

Table altered.

09:56:35 SQL> select count(1) from part partition (p_del);

  COUNT(1)
----------
    157312

09:56:46 SQL> select count(1) from part partition (p1);

  COUNT(1)
----------
    161376

09:56:50 SQL> select 157312+161376 from dual;

157312+161376
-------------
       318688

So we’ve got the initial number of rows, evenly split across partitions. This is great! Unfotunately there is a slight caveat with the split partition command – it’s very heavy on resources (remember the multi step approach?). Each row in the source partition will be copied to the destination partitions (new segments), causing a large number of IOs.

09:57:33 SQL> alter table part split partition p1 at (8500) into (partition p1, partition p1bis)
09:57:37   2   update global indexes parallel 4;

Dropping partitions is easy too:

SQL> alter table part drop partition p_del

Table altered.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: