Oracle Exchange Partition

The Oracle Exchange Partition is a method allowing to :

• exchange the data segments from a non partitioned table to a partitioned table
• exchange the data segments from a partitioned table to a non partitioned table.

This method is useful because:

• You can get data quickly in or out of partitioned table
• You can convert non partitioned table to a partitioned table or vice versa.

Let’s see how it works:

First of all, let’s create a dimension table (reference table which contains region id) and a non partitioned table with 10 million of rows:

--Table HR.DIM1 : My Dimension Table
SQL> desc hr.dim1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 REGION                                             VARCHAR2(50)

SQL> select * from hr.dim1;

        ID REGION
---------- --------------------------------------------------
         1 EUROPE
         2 AFRICA
         3 ASIA
         4 OCEANIA
         5 AMERICA

SQL>

--Table HR.MYTABLE: Contains data by region, my future table partitioned
SQL> desc hr.MYTABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 CREATED_DATE                                       DATE
 REGION_ID                                          NUMBER(10)
 DATA                                               VARCHAR2(50)

SQL>

--I populate my table HR.MYTABLE with 100 millions of rows
SQL> DECLARE
  l_region_id    hr.dim1.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 10000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_region_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_region_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_region_id   := 3;
    END IF;

    INSERT INTO HR.MYTABLE (id, created_date, region_id, data)
    VALUES (i, l_create_date, l_region_id, 'Data for Region ' || i);
  END LOOP;
  COMMIT;
END;
 22  /

PL/SQL procedure successfully completed.

SQL> select count(*) from HR.MYTABLE;

  COUNT(*)
----------
  10000000

SQL> exec dbms_stats.gather_table_stats('HR','MYTABLE');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('HR','DIM1');

Some constraints are created :

• A Primary Key on the column MYTABLE.ID
• Two Indexes on the columns MYTABLE.CREATED_DATE and MYTABLE.REGION_ID
• A Foreign Key on the column MYTABLE.REGION_ID referencing the column DIM1.ID

One more precision, a foreign key column must always be indexed in order to avoid Full Table Lock.

ALTER TABLE HR.MYTABLE ADD (
  CONSTRAINT TABLE_NP_PK PRIMARY KEY (id));

Table altered.

SQL> CREATE INDEX HR.TNP_created_date_i ON HR.MYTABLE(created_date);

Index created.

SQL> CREATE INDEX HR.TNP_region_fk_i ON HR.MYTABLE(region_id);

Index created.


ALTER TABLE HR.MYTABLE ADD (
  CONSTRAINT TNP_region_fk
  FOREIGN KEY (region_id)
  REFERENCES hr.dim1(id)
  5  );

Table altered.

Now let’s suppose we want to partition the table HR.MYTABLE.

First of all, let’s create an empty table partitioned by RANGE : HR.TABLE_PART (yearly partition based on partition key “created_date”) with the same structure than HR.MYTABLE :

CREATE TABLE HR.TABLE_PART (
  id            NUMBER(10),
  created_date  DATE,
  region_id     NUMBER(10),
  data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION TABLE_PART_2020 VALUES LESS THAN (MAXVALUE));

Table created.

ALTER TABLE HR.TABLE_PART ADD (
  CONSTRAINT TP_pk PRIMARY KEY (id)
 );

Table altered.

CREATE INDEX TP_created_date_i ON HR.TABLE_PART(created_date) LOCAL;

Index created.

CREATE INDEX TP_created_date_i ON HR.TABLE_PART(created_date) LOCAL;


SQL> SQL> CREATE INDEX TP_dim1_fk_i ON HR.TABLE_PART(region_id) LOCAL;

Index created.

ALTER TABLE HR.TABLE_PART ADD (
  CONSTRAINT tp_dim1_fk
  FOREIGN KEY (region_id)
  REFERENCES hr.dim1(id)
  5  );

Table altered.

All the rows are between 10.2020 and 10.2022, the goal is to move data related to years 2020, 2021 and 2022 (table HR.MYTABLE) into their own year partition (table HR.TABLE_PART).

SQL> select min(created_date) from hr.MYTABLE;

MIN(CREATED_DATE)
-------------------
21.10.2020 07:37:58

SQL> select max(created_date) from hr.MYTABLE;

MAX(CREATED_DATE)
-------------------
21.10.2022 07:40:36

Now let’s move the data segments with Oracle Exchange Partition:

SQL> select count(*) from HR.TABLE_PART;

  COUNT(*)
----------
         0

ALTER TABLE HR.TABLE_PART
EXCHANGE PARTITION TABLE_PART_2020
WITH TABLE HR.MYTABLE
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;

Table altered.

SQL> select count(*) from HR.TABLE_PART;

  COUNT(*)
----------
  10000000

All the rows have been moved to the partitioned table, so the non partitioned table (HR.MYTABLE) can be removed and the table partitioned + constraints and indexes renamed (HR.TABLE_PART –> HR.MYTABLE):

--Drop table HR.MYTABLE
SQL> DROP TABLE HR.MYTABLE;

Table dropped.

--Rename Table HR.TABLE_PART TO HR.MYTABLE
SQL> sho user
USER is "HR"
SQL> RENAME TABLE_PART TO MYTABLE;

Table renamed.

SQL>

--RENAME CONSTRAINTS
SQL> ALTER TABLE HR.MYTABLE RENAME CONSTRAINT TP_PK TO TABLE_NP_PK;

Table altered.

SQL> ALTER TABLE HR.MYTABLE RENAME CONSTRAINT TP_DIM1_FK TO TNP_REGION_FK;

Table altered.

SQL>

--RENAME INDEXES
SQL> ALTER INDEX TP_PK RENAME TO TABLE_NP_PK;

Index altered.

SQL> ALTER INDEX TP_DIM1_FK_I RENAME TO TNP_REGION_FK_I;

Index altered.

SQL> ALTER INDEX TP_CREATED_DATE_I RENAME TO TNP_CREATED_DATE_I;

Index altered.

Now I want to split all rows which are into 2020 PARTITION into their correct partition:

• Rows with created_date in 2020 into PARTITION 2020
• Rows with created_date in 2021 into PARTITION 2021
• Rows with created_date in 2022 into PARTITION 2022

--Move all rows with created_date in 2021 into PARTITION 2021
SQL> set timing on
ALTER TABLE HR.MYTABLE
  SPLIT PARTITION TABLE_PART_2020 AT (TO_DATE('31-DEC-2021 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION TABLE_PART_2021,
        PARTITION TABLE_PART_2020)
UPDATE GLOBAL INDEXES;

Table altered.

Elapsed: 00:01:58.67

--Move all rows with created_date in 2022 into PARTITION 2022
ALTER TABLE HR.MYTABLE
  SPLIT PARTITION TABLE_PART_2020 AT (TO_DATE('31-DEC-2022 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION TABLE_PART_2022,
        PARTITION TABLE_PART_2020)
  5    UPDATE GLOBAL INDEXES;

Table altered.

Elapsed: 00:00:00.66


SQL> EXEC DBMS_STATS.gather_table_stats('HR', 'mytable', cascade => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.49

Now, let’s check if the data are created into the correct partition:

--For 2020
SQL> select count(*) from hr.mytable partition(TABLE_PART_2020);

  COUNT(*)
----------
   3333333

Elapsed: 00:00:00.04
SQL>   select min(created_date) from hr.mytable partition(TABLE_PART_2020);

MIN(CREATED_DATE)
-------------------
21.10.2020 07:37:58

Elapsed: 00:00:00.08
SQL>   select max(created_date) from hr.mytable partition(TABLE_PART_2020);

MAX(CREATED_DATE)
-------------------
21.10.2020 07:40:36

Elapsed: 00:00:00.09

--For 2021
SQL> select count(*) from hr.mytable partition(TABLE_PART_2021);

  COUNT(*)
----------
   3333334

Elapsed: 00:00:00.04
SQL>   select min(created_date) from hr.mytable partition(TABLE_PART_2021);

MIN(CREATED_DATE)
-------------------
21.10.2021 07:37:58

Elapsed: 00:00:00.09
SQL> select max(created_date) from hr.mytable partition(TABLE_PART_2021);

MAX(CREATED_DATE)
-------------------
21.10.2021 07:40:36

Elapsed: 00:00:00.09

--For 2022
SQL> select count(*) from hr.mytable partition(TABLE_PART_2022);

  COUNT(*)
----------
   3333333

Elapsed: 00:00:00.04
SQL>   select min(created_date) from hr.mytable partition(TABLE_PART_2022);

MIN(CREATED_DATE)
-------------------
21.10.2022 07:37:58

Elapsed: 00:00:00.09
SQL>   select max(created_date) from hr.mytable partition(TABLE_PART_2022);

MAX(CREATED_DATE)
-------------------
21.10.2022 07:40:36

Elapsed: 00:00:00.09

One advantage of Oracle Partitionning is the partition pruning which dramatically reduces the amount of data retrieved from disk and shortens processing time, thus improving query performance and optimizing resource utilization (source : Benefit of Partition Pruning).

Let’s see how to check if Partition Pruning is used:

SQL> EXPLAIN PLAN FOR select data,created_date from hr.mytable where CREATED_DATE=to_date('21.10.2022 07:40:36','dd.mm.yyyy hh24:mi:ss');

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 175839893

--------------------------------------------------------------------------------
-------------------

| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time
  | Pstart| Pstop |


--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          | 17040 |   532K|  5803   (1)| 00:00:0
1 |       |       |

|   1 |  PARTITION RANGE SINGLE|          | 17040 |   532K|  5803   (1)| 00:00:0
1 |     3 |     3 |

|*  2 |   TABLE ACCESS FULL    | MYTABLE| 17040 |   532K|  5803   (1)| 00:00:0
1 |     3 |     3 |

--------------------------------------------------------------------------------
-------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CREATED_DATE"=TO_DATE(' 2022-10-21 07:40:36', 'syyyy-mm-dd hh24:m
i:ss'))


14 rows selected.

Elapsed: 00:00:00.12
SQL>

In the execution plan, we see in the plan columns PSTART and PSTOP that only PARTITION number 3 is access meaning partition pruning is used.