印刻数据库

外观

Oracle分区表设计 ​

分区表是Oracle数据库中用于管理大数据量的重要特性，通过将大表分成多个小的分区，可以提高查询性能，便于管理和维护。本文将详细介绍Oracle分区表的设计、创建和管理。

分区表概述 ​

什么是分区表？ ​

分区表是将一个大表按照某种规则分成多个小的子表，每个子表称为一个分区。分区表在物理上是多个表，但在逻辑上是一个表。用户可以像访问普通表一样访问分区表，但Oracle会自动将请求路由到对应的分区。

分区表的优势 ​

  • 提高查询性能：只需要扫描相关分区，而不是整个表
  • 便于管理和维护：可以对单个分区进行操作，如备份、恢复、删除等
  • 提高可用性：单个分区的故障不会影响整个表
  • 支持大数据量：可以处理超过TB级别的数据
  • 优化资源使用：可以将不同分区存储在不同的表空间或存储设备上

分区表的适用场景 ​

  • 大表：表大小超过10GB
  • 历史数据：需要按时间归档的历史数据
  • 查询模式：经常按特定列查询，如日期、区域等
  • 维护需求：需要定期进行数据加载、删除或归档
  • 高可用性要求：需要最小化停机时间

分区类型 ​

Oracle支持多种分区类型，每种类型适用于不同的场景。

范围分区（Range Partitioning） ​

范围分区是最常用的分区类型，根据列值的范围将表分成多个分区。

特点 ​

  • 适用于按时间或连续值分区
  • 分区键通常是日期、时间或数值列
  • 新数据可以自动添加到新的分区

示例 ​

sql
-- 创建按日期范围分区的表
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    product_id NUMBER,
    quantity NUMBER,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION sales_q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION sales_q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION sales_q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

列表分区（List Partitioning） ​

列表分区根据列值的离散值将表分成多个分区。

特点 ​

  • 适用于按离散值分区，如区域、状态、类型等
  • 分区键通常是字符串或枚举类型的列
  • 每个分区包含特定的列值

示例 ​

sql
-- 创建按区域列表分区的表
CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    order_date DATE,
    region VARCHAR2(20),
    amount NUMBER
)
PARTITION BY LIST (region) (
    PARTITION orders_north VALUES ('NORTH', 'NORTHEAST', 'NORTHWEST'),
    PARTITION orders_south VALUES ('SOUTH', 'SOUTHEAST', 'SOUTHWEST'),
    PARTITION orders_east VALUES ('EAST'),
    PARTITION orders_west VALUES ('WEST'),
    PARTITION orders_other VALUES (DEFAULT)
);

哈希分区（Hash Partitioning） ​

哈希分区使用哈希函数将数据均匀分布到多个分区中。

特点 ​

  • 适用于数据分布均匀的场景
  • 自动分布数据，无需手动管理分区范围
  • 提高并发性能
  • 适合随机访问的数据

示例 ​

sql
-- 创建哈希分区的表
CREATE TABLE customers (
    customer_id NUMBER,
    customer_name VARCHAR2(100),
    email VARCHAR2(100),
    phone VARCHAR2(20)
)
PARTITION BY HASH (customer_id)
PARTITIONS 8;

复合分区（Composite Partitioning） ​

复合分区是将两种或多种分区类型结合使用，如范围-哈希、范围-列表、列表-哈希等。

常见复合分区类型 ​

  1. 范围-哈希分区：先按范围分区，再按哈希分区
  2. 范围-列表分区：先按范围分区，再按列表分区
  3. 列表-哈希分区：先按列表分区，再按哈希分区
  4. 列表-范围分区：先按列表分区，再按范围分区

示例：范围-哈希分区 ​

sql
-- 创建范围-哈希复合分区的表
CREATE TABLE sales_composite (
    sale_id NUMBER,
    sale_date DATE,
    product_id NUMBER,
    quantity NUMBER,
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (product_id)
SUBPARTITIONS 4
(
    PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION sales_q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION sales_q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION sales_q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

示例：范围-列表分区 ​

sql
-- 创建范围-列表复合分区的表
CREATE TABLE sales_region (
    sale_id NUMBER,
    sale_date DATE,
    region VARCHAR2(20),
    product_id NUMBER,
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region)
(
    PARTITION p_q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_q1_2023_north VALUES ('NORTH', 'NORTHEAST'),
        SUBPARTITION p_q1_2023_south VALUES ('SOUTH', 'SOUTHEAST'),
        SUBPARTITION p_q1_2023_west VALUES ('WEST'),
        SUBPARTITION p_q1_2023_east VALUES ('EAST')
    ),
    PARTITION p_q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_q2_2023_north VALUES ('NORTH', 'NORTHEAST'),
        SUBPARTITION p_q2_2023_south VALUES ('SOUTH', 'SOUTHEAST'),
        SUBPARTITION p_q2_2023_west VALUES ('WEST'),
        SUBPARTITION p_q2_2023_east VALUES ('EAST')
    )
);

间隔分区（Interval Partitioning） ​

间隔分区是范围分区的扩展，当插入的数据超出现有分区范围时，Oracle会自动创建新的分区。

特点 ​

  • 自动管理分区的创建
  • 适用于按时间增长的数据
  • 减少手动管理分区的工作量

示例 ​

sql
-- 创建按月份间隔分区的表
CREATE TABLE sales_interval (
    sale_id NUMBER,
    sale_date DATE,
    product_id NUMBER,
    quantity NUMBER,
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION p_initial VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);

引用分区（Reference Partitioning） ​

引用分区是基于父表的分区键对子表进行分区，子表的分区与父表的分区保持一致。

特点 ​

  • 子表分区自动与父表分区同步
  • 简化分区管理
  • 提高连接查询性能

示例 ​

sql
-- 创建父表（范围分区）
CREATE TABLE orders_ref (
    order_id NUMBER PRIMARY KEY,
    order_date DATE,
    customer_id NUMBER,
    total_amount NUMBER
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION p_initial VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);

-- 创建子表（引用分区）
CREATE TABLE order_items_ref (
    order_item_id NUMBER,
    order_id NUMBER REFERENCES orders_ref(order_id),
    product_id NUMBER,
    quantity NUMBER,
    unit_price NUMBER,
    PRIMARY KEY (order_item_id, order_id)
)
PARTITION BY REFERENCE (orders_ref_fk);

虚拟列分区（Virtual Column Partitioning） ​

虚拟列分区是基于虚拟列的值进行分区，虚拟列是通过表达式计算得到的列，不实际存储数据。

特点 ​

  • 基于表达式的值进行分区
  • 减少存储空间
  • 提高查询灵活性

示例 ​

sql
-- 创建虚拟列分区的表
CREATE TABLE sales_virtual (
    sale_id NUMBER,
    sale_date DATE,
    product_id NUMBER,
    quantity NUMBER,
    amount NUMBER,
    -- 虚拟列：计算季度
    sale_quarter AS (TO_CHAR(sale_date, 'Q')) VIRTUAL
)
PARTITION BY LIST (sale_quarter)
(
    PARTITION p_q1 VALUES ('1'),
    PARTITION p_q2 VALUES ('2'),
    PARTITION p_q3 VALUES ('3'),
    PARTITION p_q4 VALUES ('4')
);

分区键选择 ​

分区键选择原则 ​

  1. 查询模式：选择经常用于WHERE子句的列
  2. 数据分布：确保数据均匀分布到各个分区
  3. 维护需求：考虑数据加载、删除和归档的需求
  4. 连接性能：如果经常与其他表连接，考虑使用相同的分区键
  5. 唯一性：分区键应包含主键或唯一键的一部分

不适合作为分区键的列 ​

  • 低选择性的列（如性别、状态等）
  • 频繁更新的列
  • 包含NULL值较多的列

分区表创建与管理 ​

创建分区表 ​

sql
-- 基本语法
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    constraint constraints
)
PARTITION BY [RANGE|LIST|HASH|...](partition_key)
[PARTITIONS number_of_partitions]
[PARTITION partition_name VALUES LESS THAN (value),
 PARTITION partition_name VALUES LESS THAN (value),
 ...]
[TABLESPACE tablespace_name];

修改分区表 ​

添加分区 ​

sql
-- 为范围分区表添加分区
ALTER TABLE sales ADD PARTITION sales_q1_2024 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));

-- 为列表分区表添加分区
ALTER TABLE orders ADD PARTITION orders_asia VALUES ('ASIA');

-- 为哈希分区表添加分区
ALTER TABLE customers ADD PARTITION;

删除分区 ​

sql
-- 删除分区（同时删除数据）
ALTER TABLE sales DROP PARTITION sales_q1_2023;

-- 删除分区但保留数据（仅适用于哈希和列表分区）
ALTER TABLE customers MERGE PARTITIONS FOR (1000) INTO PARTITION p_merged;

合并分区 ​

sql
-- 合并范围分区
ALTER TABLE sales MERGE PARTITIONS sales_q1_2023, sales_q2_2023 INTO PARTITION sales_h1_2023;

-- 合并列表分区
ALTER TABLE orders MERGE PARTITIONS orders_north, orders_south INTO PARTITION orders_north_south;

拆分分区 ​

sql
-- 拆分范围分区
ALTER TABLE sales SPLIT PARTITION sales_h1_2023 AT (TO_DATE('2023-04-01', 'YYYY-MM-DD'))
INTO (PARTITION sales_q1_2023, PARTITION sales_q2_2023);

-- 拆分列表分区
ALTER TABLE orders SPLIT PARTITION orders_north_south VALUES ('NORTH', 'NORTHEAST') INTO 
(PARTITION orders_north_new, PARTITION orders_south_new);

重命名分区 ​

sql
-- 重命名分区
ALTER TABLE sales RENAME PARTITION sales_q1_2023 TO sales_quarter1_2023;

移动分区 ​

sql
-- 将分区移动到新的表空间
ALTER TABLE sales MOVE PARTITION sales_q1_2023 TABLESPACE users;

截断分区 ​

sql
-- 截断分区（删除分区中的数据，但保留分区结构）
ALTER TABLE sales TRUNCATE PARTITION sales_q1_2023;

查看分区表信息 ​

sql
-- 查看用户所有分区表
SELECT table_name, partitioning_type, partition_count
FROM user_part_tables;

-- 查看分区表的分区信息
SELECT table_name, partition_name, high_value, tablespace_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'SALES';

-- 查看子分区信息
SELECT table_name, partition_name, subpartition_name, high_value, tablespace_name
FROM user_tab_subpartitions
WHERE table_name = 'SALES_COMPOSITE';

-- 查看分区键信息
SELECT table_name, column_name, column_position
FROM user_part_key_columns
WHERE table_name = 'SALES';

分区表最佳实践 ​

设计阶段 ​

  1. 选择合适的分区类型：

    • 按时间或连续值：范围分区
    • 按离散值：列表分区
    • 数据分布均匀：哈希分区
    • 复杂查询模式：复合分区

  2. 合理设计分区大小：

    • 每个分区大小建议在10GB到100GB之间
    • 避免过多的小分区

  3. 考虑分区键的选择：

    • 选择经常用于查询的列
    • 确保数据均匀分布
    • 考虑未来的扩展需求

  4. 使用间隔分区简化管理：

    • 对于按时间增长的数据，使用间隔分区自动管理分区

维护阶段 ​

  1. 定期收集统计信息：

    sql
    EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR',
    tabname => 'SALES',
    cascade => TRUE,
    granularity => 'PARTITION'
);

  2. 监控分区使用情况：

    • 定期检查分区大小和数据分布
    • 识别热点分区

  3. 定期清理旧数据：

    sql
    -- 删除旧分区
ALTER TABLE sales DROP PARTITION sales_q1_2023;

-- 或者截断旧分区
ALTER TABLE sales TRUNCATE PARTITION sales_q1_2023;

  4. 使用分区交换加载数据：

    sql
    -- 创建临时表
CREATE TABLE sales_temp AS SELECT * FROM sales WHERE 1=0;

-- 加载数据到临时表
INSERT INTO sales_temp SELECT * FROM external_sales_data;

-- 交换分区
ALTER TABLE sales EXCHANGE PARTITION sales_q1_2024 WITH TABLE sales_temp WITHOUT VALIDATION;

查询优化 ​

  1. 使用分区裁剪：

    • 在查询中包含分区键，让Oracle只扫描相关分区
    sql
    -- 好：使用分区键，Oracle会只扫描2023年第一季度的分区
SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-03-31';

-- 差：不使用分区键，Oracle需要扫描所有分区
SELECT * FROM sales WHERE product_id = 100;

  2. 使用并行查询：

    sql
    -- 启用并行查询
SELECT /*+ PARALLEL(sales, 4) */ * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

  3. 优化连接查询：

    • 对相关表使用相同的分区键，提高连接性能
    • 使用分区连接提示
    sql
    SELECT /*+ PARTITION(s, p1, p2) */ * 
FROM sales s JOIN products p ON s.product_id = p.product_id
WHERE s.sale_date BETWEEN '2023-01-01' AND '2023-03-31';

版本差异 ​

Oracle 11g及之前 ​

  • 支持基本的范围、列表、哈希和复合分区
  • 不支持间隔分区和引用分区
  • 分区表功能有限

Oracle 12c ​

  • 引入间隔分区和引用分区
  • 增强复合分区功能
  • 支持在线分区操作
  • 引入可插拔数据库（PDB）中的分区管理

Oracle 18c ​

  • 增强分区功能，包括混合分区
  • 引入自动列表分区
  • 增强分区维护功能

Oracle 19c ​

  • 长期支持版本
  • 改进分区性能
  • 增强分区管理功能
  • 支持更多分区类型

Oracle 21c ​

  • 引入异步分区维护
  • 增强分区裁剪功能
  • 改进分区统计信息收集

常见问题（FAQ） ​

Q: 如何选择合适的分区类型？ ​

A: 选择分区类型应考虑：

  • 数据分布特点：连续值使用范围分区，离散值使用列表分区，均匀分布使用哈希分区
  • 查询模式：经常按范围查询使用范围分区，经常按值查询使用列表分区
  • 维护需求：需要自动管理分区使用间隔分区

Q: 分区表和普通表的区别是什么？ ​

A: 分区表和普通表的主要区别：

  • 物理存储：分区表存储在多个分区中，普通表存储在一个段中
  • 查询性能：分区表可以只扫描相关分区，普通表需要扫描整个表
  • 维护：分区表可以对单个分区进行操作，普通表只能对整个表操作
  • 可用性：单个分区的故障不会影响整个分区表，普通表故障会影响整个表

Q: 如何将普通表转换为分区表？ ​

A: 将普通表转换为分区表的方法：

  1. 使用CREATE TABLE AS SELECT语句创建分区表
  2. 使用在线重定义功能（DBMS_REDEFINITION）
  3. 使用交换分区功能

Q: 分区表的最大分区数量是多少？ ​

A: Oracle 12c及以上版本支持的最大分区数量为1048575个。

Q: 如何监控分区表的性能？ ​

A: 监控分区表性能的方法：

  • 查看执行计划，确认是否使用了分区裁剪
  • 监控分区访问情况，识别热点分区
  • 收集和分析分区统计信息
  • 使用AWR报告分析分区表性能

Q: 什么是分区裁剪？ ​

A: 分区裁剪是指Oracle在执行查询时，只扫描与查询条件匹配的分区，而不是整个表。分区裁剪可以显著提高查询性能。

Q: 如何处理分区表中的NULL值？ ​

A: 处理分区表中NULL值的方法：

  • 范围分区：NULL值会被放入第一个分区
  • 列表分区：可以使用DEFAULT分区存储NULL值
  • 哈希分区：NULL值会被分配到一个特定的分区

Q: 分区表是否支持索引？ ​

A: 是的，分区表支持索引，包括：

  • 本地分区索引：索引分区与表分区一一对应
  • 全局分区索引：索引分区与表分区不一一对应
  • 全局非分区索引：整个索引作为一个整体

总结 ​

分区表是Oracle数据库中管理大数据量的重要特性，通过合理的分区设计，可以提高查询性能，便于管理和维护。在设计分区表时，应根据数据分布特点、查询模式和维护需求选择合适的分区类型和分区键。

Oracle支持多种分区类型，包括范围分区、列表分区、哈希分区、复合分区、间隔分区等，每种类型适用于不同的场景。在实际应用中，应根据具体业务需求选择合适的分区策略。

定期维护分区表，包括收集统计信息、监控分区使用情况、清理旧数据等，可以确保分区表的高性能和可用性。随着Oracle版本的更新，分区表功能不断增强，如间隔分区、引用分区、自动列表分区等，这些功能可以进一步简化分区管理，提高系统性能。