Partition Exchange by Example

One of the great features about partitioning, and most specifically range-based partitioning, is the ability to load data quickly and easily with minimal impact on the current users using:

alter table call exchange partition data_2007 with table call_temp;

This command swaps over the definitions of the named partition and the CALL table, so that the data suddenly exists in the right place in the partitioned table. Moreover, with the inclusion of the two optional extra clauses, index definitions will be swapped and Oracle will not check whether the data actually belongs in the partition - so the exchange is very quick.

Below is an example where it is observed that while moving 180 MB of data:

(1) Create as select method it takes around 15 seconds
(2) Partition exchange method is taking only three seconds.

The partition exchange method also takes care of the index on the source table. So efficiency of the partition exchange is more than five times.

Test Instance
Server : erpdvdb.ea.com (HP-UX B.11.11 U 9000/800)
Database : HRDEV (8.1.7.3.0)
Schema : SAGUHA (with DBA privilege)
Source Table : CALL_TEMP
Destination Table : CALL (with partition)

Start with a clean schema

DROP TABLE call;
DROP TABLE call_temp;

col OBJECT_NAME for a20
select owner||’.’||segment_name OBJECT_NAME, round(bytes/1024/1024,1) MB from dba_segments where owner = ‘SAGUHA’ and segment_name like ‘%CALL%’;

no rows selected

Start time keeping

set timing on

Create the temporary (source) Table which is used to load the data offline
DROP TABLE call_temp;
CREATE TABLE call_temp (
id NUMBER(12,6),
v1 VARCHAR2(10),
data VARCHAR2(100)
);

Load 1'000'000 Rows into the offline (source) table
INSERT /*+ append ordered full(s1) use_nl(s2) */
INTO call_temp
SELECT
TRUNC((ROWNUM-1)/500,6),
TO_CHAR(ROWNUM),
RPAD('X',100,'X')
FROM
all_tables s1,
all_tables s2
WHERE
ROWNUM <= 1000000;
commit;

Elapsed: 00:05:44.09

Check size of the table
col OBJECT_NAME for a20
select owner||’.’||segment_name OBJECT_NAME, round(bytes/1024/1024,1) MB from dba_segments where owner = ‘SAGUHA’ and segment_name like ‘%CALL%’;

--------------------------------\
OBJECT_NAME MB
-------------------- ----------
SAGUHA.CALL_TEMP 189.8
--------------------------------/

Add Primary Key to the offline Table
--ALTER TABLE call_temp
--ADD CONSTRAINT pk_call_temp PRIMARY KEY(id)
--USING INDEX (CREATE INDEX pk_call_temp ON call_temp(id) NOLOGGING);

CREATE INDEX pk_call_temp ON call_temp(id) NOLOGGING;
--(Elapsed: 00:00:06.09)
ALTER TABLE call_temp
ADD CONSTRAINT pk_call_temp PRIMARY KEY(id);
--(Elapsed: 00:00:07.04)

-------------------------------\
OBJECT_NAME MB
-------------------- ----------
SAGUHA.CALL_TEMP 189.8
SAGUHA.PK_CALL_TEMP 25
-------------------------------/

Case I: Create destination table directly
=========================================
drop table call;
create table call as select * from call_temp;

Elapsed: 00:00:14.03 --I

Case II: Create the partition (destination) table
=================================================
DROP TABLE call;
CREATE TABLE call (
id NUMBER(12,6),
v1 VARCHAR2(10),
data VARCHAR2(100)
)
PARTITION BY RANGE(id) ( -- Partion Key = Primary Key
PARTITION call_partition VALUES LESS THAN (MAXVALUE)
);

------------------------------------------\
OBJECT_NAME MB
-------------------- ----------
SAGUHA.CALL_TEMP 189.8
SAGUHA.CALL 0
SAGUHA.PK_CALL_TEMP 25
------------------------------------------/

Add local primary key to the partition table as a local index (destination)
--ALTER TABLE call
--ADD CONSTRAINT pk_call PRIMARY KEY(id)
--USING INDEX (CREATE INDEX pk_call ON CALL(id) NOLOGGING LOCAL);
CREATE INDEX pk_call ON CALL(id) NOLOGGING LOCAL;
ALTER TABLE call
ADD CONSTRAINT pk_call PRIMARY KEY(id);

--------------------------------\
OBJECT_NAME MB
-------------------- ----------
SAGUHA.CALL_TEMP 189.8
SAGUHA.CALL 0
SAGUHA.PK_CALL_TEMP 25
SAGUHA.PK_CALL 0
--------------------------------/

Now swap the offline Table into the partition (source into destination)
=======================================================================
ALTER TABLE CALL
EXCHANGE PARTITION call_partition WITH TABLE call_temp
INCLUDING INDEXES
WITHOUT VALIDATION;

Elapsed: 00:00:02.06 --II
Check size of the table
col OBJECT_NAME for a20
select owner||’.’||segment_name OBJECT_NAME, round(bytes/1024/1024,1) MB from dba_segments where owner = ‘SAGUHA’ and segment_name like ‘%CALL%’;

----------------------------------------\
OBJECT_NAME MB
-------------------- ----------
SAGUHA.CALL_TEMP 0
SAGUHA.CALL 189.8
SAGUHA.PK_CALL_TEMP 0
SAGUHA.PK_CALL 25
----------------------------------------/

So data from main table as well as index moved to the destination.

Conclusion: The exchange partition method is 14.03/02.06 = 6.8 times faster than normal insert method.