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.01

Oracle 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 INDEXES in 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 LOCAL

CREATE 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 INDEXES is needed because without it that would leave the global indexes associated with the partition in an UNUSABLE state. If the UPDATE GLOBAL INDEXES clause 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.