How to load Data very fast using Partition Exchange
Overview
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.
Example with no Parent Child Relation
In this example, the primary key and the partition key in the partitioned table CALL both are build on the same column «id». Therefore the primary key on CALL can be a LOCAL index and the indexes doesn’t become UNUSABLE (must not be rebuild) after the EXCHANGE.
– Create the Partition Table (Destination Table)
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)
);– Next, create the temporary Table which is used to load the Data offline
CREATE TABLE call_temp (
id NUMBER(12,6),
v1 VARCHAR2(10),
data VARCHAR2(100)
);– Load 1′000′000 Rows into the offline 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;– Add LOCAL Primary Key to the Partition Table as a local Index
ALTER TABLE call
ADD CONSTRAINT pk_call PRIMARY KEY(id)
USING INDEX (CREATE INDEX pk_call ON CALL(id) NOLOGGING LOCAL);– 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);– Now swap the offline Table into the Partition
ALTER TABLE CALL
EXCHANGE PARTITION call_partition WITH TABLE call_temp
INCLUDING INDEXES
WITHOUT VALIDATION;Elapsed: 00:00:00.01Oracle is checking that the exchange won’t cause a uniqueness problem. The query is searching the entire CALL table (excluding the partition we are exchanging) to see if there are any duplicates of the rows which we are loading. This is particularly daft, since the unique constraint is maintained through a local index, so it must include the partitioning key ID, which means there is only one legal partition in which a row can be, and we have already promised (through the without validation clause) that all the rows belong where we are putting them.
– For the next load, first truncate the CALL Table, then process the next load.
TRUNCATE TABLE call;
ALTER TABLE CALL
EXCHANGE PARTITION call_partition WITH TABLE call_temp
INCLUDING INDEXES
WITHOUT VALIDATION;
Example with Parent Child Relation
In this example, the primary key and the partition key in the partitioned table CALL are NOT the same. The primary key is build on the column «id», but the partition key is build on «created_date». Therefore the primary key on CALL must be a GLOBAL index and the indexes must be maintained using the clause
UPDATE GLOBAL INDEXESin the EXCHANGE.– Create and populate a small lookup table
CREATE TABLE lookup (
id NUMBER(10),
description VARCHAR2(50)
);ALTER TABLE lookup ADD (
CONSTRAINT pk_lookup PRIMARY KEY (id)
);INSERT INTO lookup (id, description) VALUES (1, ‘ONE’);
INSERT INTO lookup (id, description) VALUES (2, ‘TWO’);
INSERT INTO lookup (id, description) VALUES (3, ‘THREE’);
COMMIT;– Create and populate a temporary table to load the data
CREATE TABLE call_temp (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);– Load the temporary table
DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO call_temp (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, ‘Loaded Data for ‘ || i);
END LOOP;
COMMIT;
END;
/– Apply Primary Key to the CALL_TEMP table.
ALTER TABLE call_temp ADD (
CONSTRAINT pk_call_temp PRIMARY KEY (id)
);– Add Key and Foreign Key Constraint to the CALL_TEMP table.
CREATE INDEX idx_call_temp_created_date ON call_temp(created_date);
CREATE INDEX idx_call_temp_lookup_id ON call_temp(lookup_id);ALTER TABLE call_temp ADD (
CONSTRAINT fk_call_temp_lookup_id
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);– Next we create a new table with the appropriate partition structure
– to act as the destination table. The destination must have the
– same constraints and indexes defined.CREATE TABLE call (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION call_temp_created_date VALUES LESS THAN (MAXVALUE));– Add Primary Key to CALL which must be GLOBAL
ALTER TABLE call ADD
CONSTRAINT pk_call PRIMARY KEY (id)
USING INDEX (CREATE INDEX pk_call ON CALL(id) GLOBAL
);– Add Keys and Foreign Key Constraint to the CALL table
– which can be LOCALCREATE INDEX idx_call_created_date ON call(created_date) LOCAL;
CREATE INDEX idx_call_lookup_id ON call(lookup_id) LOCAL;ALTER TABLE call ADD (
CONSTRAINT fk_call_lookup_id
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);– With this destination table in place we can start the conversion.
– We now switch the segments associated with the source table and the
– partition in the destination table using EXCHANGE PARTITION
SET TIMING ON;
ALTER TABLE call
EXCHANGE PARTITION call_temp_created_date
WITH TABLE call_temp
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;Elapsed: 00:00:27.19
This is significantly slower than in the previous example!
The
UPDATE GLOBAL INDEXESis needed because without it that would leave the global indexes associated with the partition in anUNUSABLEstate. If theUPDATE GLOBAL INDEXESclause is added, the performance is reduced since the index rebuild is part of the issued DDL. The index updates are logged and it should only be used when the number of rows is low and data must stay available. For larger numbers of rows index rebuilds are more efficient and allow index reorganization.
Ida Bagus Enderajana
Recent Comments