非分区表转换为分区表的实用技术与实例

一、 转换方法介绍

将普通表转换为分区表的方式有以下四种：

1. 导出/导入（Export/import）：

- **方法**：通过Oracle的导出/导入命令实现，即把要转换的普通表数据导出，随后再导入分区表。
- **优点**：操作简便，适合数据量较小的表。
- **缺点**：导入前需要先创建好分区表结构；如果表很大，导入时间可能较长。
- **举例**：假设有一个普通表`employees`，需要将其转换为分区表`employees_partitioned`，首先通过`exp`命令导出数据，然后使用`imp`命令导入到新建的分区表中。

2. 子查询插入（Insert with a subquery）：

- **方法**：通过查询将普通表的数据插入到分区表中。例如，`insert into xx2 select * from xx1`，其中xx1为普通表，xx2为分区表。
- **优点**：最简单的方法，适合表结构简单、数据量适中的情况。
- **缺点**：如果表数据量大，插入过程可能耗时较长。
- **举例**：假设有一个普通表`orders`，需要将其转换为分区表`orders_partitioned`，可以使用以下SQL语句：
    ```sql
    INSERT INTO orders_partitioned SELECT * FROM orders;
    ```
    此操作将普通表`orders`中的所有数据插入到分区表`orders_partitioned`中。

3. 分区交换（Partition exchange）：

- **方法**：分区交换允许在表与分区表之间进行数据的交换。主要步骤包括创建分区表和普通表，然后将普通表的数据加载到分区表的分区中。
- **优点**：效率最高，因为不涉及数据的物理移动，适合大量数据的快速转换。
- **缺点**：操作较为复杂，要求表结构一致，且不支持跨分区类型的交换（如范围分区与列表分区之间）。
- **举例**：
    - 创建分区表`orders_partitioned`：
        ```sql
        CREATE TABLE orders_partitioned (
            order_id NUMBER,
            order_date DATE
        )
        PARTITION BY RANGE(order_date) (
            PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
            PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
        );
        ```
    - 创建普通表`orders_temp`用于数据交换：
        ```sql
        CREATE TABLE orders_temp AS SELECT * FROM orders WHERE order_date < TO_DATE('2023-01-01', 'YYYY-MM-DD');
        ```
    - 进行分区交换：
        ```sql
        ALTER TABLE orders_partitioned EXCHANGE PARTITION p1 WITH TABLE orders_temp WITHOUT VALIDATION;
        ```

4. 在线重定义（DBMS_REDEFINITION）：

- **方法**：通过Oracle提供的`DBMS_REDEFINITION`包，可以在不停止DML操作的情况下，将普通表转换为分区表。
- **优点**：对业务影响最小，适合需要保持系统高可用性的场景。
- **缺点**：操作较为复杂，且对系统资源要求较高。
- **举例**：
    - 重新定义表结构并开启重定义：
        ```sql
        EXEC DBMS_REDEFINITION.START_REDEF_TABLE('HR', 'orders', 'orders_partitioned', 'partitioned_column');
        ```
    - 同步数据：
        ```sql
        EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HR', 'orders', 'orders_partitioned');
        ```
    - 结束重定义：
        ```sql
        EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('HR', 'orders', 'orders_partitioned');
        ```

2. 分区交换的使用

分区交换（Partition exchange）是其中一种较为高效的方法，用于将普通表的数据转换并加载到分区表中。主要步骤如下：

1. 创建分区表，假设有两个分区（P1，P2）。
2. 普通表A的数据必须满足P1分区的规则，用A表和P1分区交换，即把A表的数据加载到P1分区。
3. 普通表B的数据必须满足P2分区的规则，用B表和P2分区交换，即把B表的数据加载到P2分区。

举例：

• 创建分区表gcc_a：

  CREATE TABLE gcc_a (
      a INTEGER
  )
  PARTITION BY RANGE(a) (
      PARTITION p1 VALUES LESS THAN (5),
      PARTITION p2 VALUES LESS THAN (9)
  );
  

• 创建普通表gcc_temp1用于分区p1：

  CREATE TABLE gcc_temp1 (a INTEGER);
  INSERT INTO gcc_temp1 VALUES (1);
  INSERT INTO gcc_temp1 VALUES (2);
  -- 创建索引
  CREATE INDEX index_gcc_temp1 ON gcc_temp1(a);
  

• 交换操作：

  ALTER TABLE gcc_a EXCHANGE PARTITION p1 WITH TABLE gcc_temp1 INCLUDING INDEXES WITHOUT VALIDATION;
  

3. 分区的在线重定义

在线重定义是指可以在表进行DDL结构变更的同时继续进行DML操作。Oracle通过DBMS_REDEFINITION包来实现这一功能，这样在大部分时间内，表依然可以正常操作，保证系统的高可用性。

举例：

• 开始重定义：

  EXEC DBMS_REDEFINITION.START_REDEF_TABLE('HR', 'employees', 'employees_partitioned');
  

• 进行数据同步：

  EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HR', 'employees', 'employees_partitioned');
  

• 完成重定义：

  EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('HR', 'employees', 'employees_partitioned');
  

通过以上方法，数据库管理员可以在系统运行的过程中，将非分区表安全、高效地转换为分区表，提高数据库的管理和操作效率。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

让AI工具成为你的得力助手，感受AI工具的无限可能，让复杂的任务变得简单，让你的工作更加轻松和高效。