Databases Reference
In-Depth Information
Exchanging a Partition with an Existing Table
Exchanging a partition is a common technique for transparently loading new data into large partitioned tables.
The technique involves taking a stand-alone table and swapping it with an existing partition (in an already partitioned
table), allowing you to add fully loaded new partitions (and associated indexes) without affecting the availability or
performance of operations against the other partitions in the table.
This simple example illustrates the process. Say you have a range-partitioned table, created as follows:
create table f_sales
(sales_amt number
,d_date_id number)
partition by range (d_date_id)
(partition p_2011 values less than (20120101),
partition p_2012 values less than (20130101),
partition p_2013 values less than (20140101));
You also create a local bitmap index on the
D_DATE_ID
column:
create bitmap index d_date_id_fk1 on
f_sales(d_date_id) local;
Now, add a new partition to the table to store new data:
alter table f_sales add partition p_2014
values less than(20150101);
Next, create a staging table, and insert data that fall within the range of values for the newly added partition:
create table workpart(
sales_amt number
,d_date_id number);
--
insert into workpart values(100,20140201);
insert into workpart values(120,20140507);
Then, create a bitmap index on the
WORKPART
table that matches the structure of the bitmap index on
F_SALES
:
create bitmap index d_date_id_fk2
on workpart(d_date_id);
Now, exchange the
WORKPART
table with the
P_2014
partition:
alter table f_sales
exchange partition p_2014
with table workpart
including indexes without validation;
A quick query of the
F_SALES
table verifies that the partition was exchanged successfully:
SQL> select * from f_sales partition(p_2014);
