Oracle 分区交换技术

Oracle 分区交换是一种允许：

• 将数据段从非分区表交换到分区表
• 将数据段从分区表交换到非分区表

这个方法很有用，因为：

• 可以快速将数据导入或导出分区表
• 可以将非分区表转换为分区表，反之亦然

让我们看看它是如何工作的：

首先，让我们创建一个维度表（包含 region id 的引用表）和一个包含 1000 万行的非分区表：

--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');

复制

创建了一些约束：

• MYTABLE.ID 列上的主键
• MYTABLE.CREATED_DATE 和 MYTABLE.REGION_ID 列上的两个索引
• MYTABLE.REGION_ID 列上的外键引用列 DIM1.ID

更精确的是，必须始终索引外键列以避免全表锁定。

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.

复制

现在假设我们要对表 HR.MYTABLE 进行分区。

首先，创建一个按 RANGE 分区的空表：HR.TABLE_PART（基于分区键“created_date”的年度分区），其结构与 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.

复制

所有行都在 10.2020 和 10.2022 之间，目标是将与 2020、2021 和 2022 年相关的数据（表 HR.MYTABLE）移动到它们自己的年份分区（表 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
复制

现在让我们使用 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

复制

所有行都已移至分区表，因此可以删除非分区表（HR.MYTABLE）并将表分区+约束和索引重命名（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.

复制

现在我想将所有进入 2020 PARTITION 的行拆分为正确的分区：

• created_date 在 2020 年进入 PARTITION 2020 的行
• 2021 年 created_date 的行进入 PARTITION 2021
• 2022 年 created_date 的行进入 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
复制

现在，让我们检查数据是否创建到正确的分区中：

--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
复制

Oracle Partitionning 的一个优点是分区修剪，它极大地减少了从磁盘检索的数据量并缩短了处理时间，从而提高了查询性能并优化了资源利用率（来源：Benefit of Partition Pruning）。

让我们看看如何检查是否使用了分区修剪：

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>
复制

在执行计划中，我们在计划列 PSTART 和 PSTOP 中看到只有 PARTITION 编号 3 是访问，这意味着使用了分区修剪。

原文标题：Oracle Exchange Partition
原文作者：Lazhar Felahi
原文地址：https://www.dbi-services.com/blog/oracle-exchange-partition/#