An Experiment of Partitioning

I was experimenting about a special partitioning task, which involves a 16 million row table. The goal is to create two partitions based on its primary key. There is a driver table containing
about 10-20% primary key values that determines which rows should go to the smaller partition.

First of all, I need to add a new column as a paritition key.

SQL> alter table user_a.target_tab add (s char(1));

Secondly, I created a partitioned destination table structure with exact same indexes and constraints as the target table.

create table user_a.target_tab_p
partition by list (s)
(
partition p1 values('Y'),
partition p2 values(default)
)
as
select * from user_a.target_tab
where 1=0;


Then, I performed exchanging partition,it took about 30 min, with the following command:

alter table user_a.target_tab_p
exchange partition p2
with table user_a.target_tab
without validation
update global index
parallel 8 ;


Next, I drop the original table, rename the partitioned table, index and constraint:

SQL> drop table user_a.target_tab;
SQL> alter table user_a_target_tab_p rename to target_tab;

In order to be able to update the partition key, I have to enable row movement:

SQL> alter table user_a.target_tab enable row movement;

I used the following command to update the partition key, it took about 20 min:

update user_a.target_tab set s='Y'
where id in (
SELECT id FROM driver_tab;
);

After analyze, the following query verified the successful partition:

SELECT table_owner, table_name, partition_name, num_rows
FROM dba_tab_partitions
WHERE table_name = 'TARGET_TAB'
and table_owner='USER_A'
/

TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS
------------------ ----------------- -------------------- ----------
USER_A TARGET_TAB P1 1983143
USER_A TARGET_TAB P2 14124980


The whole process took less than 1 hour.


Reference: Partitioning an Existing Table using EXCHANGE PARTITION