🌑

☀️

Hi Folks.

主页文章关于正在前往 Planet 🎉

详解：Oracle分区原理及实战

Oracle 数据库分区是一种物理数据组织方式，主要用于管理和优化大规模表。通过将大表分成更小、更易管理的部分（即分区），可以显著提高查询性能、简化管理任务并支持更高的可用性。

一、Oracle 分区原理

分区类型：
- 范围分区（Range Partitioning）：基于列值的范围进行分区，例如按日期或编号范围。
- 列表分区（List Partitioning）：基于列值的预定义列表进行分区。
- 哈希分区（Hash Partitioning）：基于哈希函数计算出的散列值进行分区。
- 复合分区（Composite Partitioning）：结合以上两种或多种类型的分区方法，如范围 - 列表复合分区。
子分区：
在每个分区内部还可以进一步划分成多个子分区，实现更细致的数据分割。
全局索引与本地索引：
- 全局索引对整个表的所有分区生效。
- 本地索引只针对单个分区有效，对于大型分区表，通常推荐使用本地索引以节省空间和提高查询效率。
分区维护：
可以单独对某个分区进行添加、删除、合并、拆分等操作，无需影响整个表的其他部分，从而提升维护效率和在线服务的连续性。

二、实践应用

在 Oracle 数据库中进行表分区，可以显著提高大型表的查询性能、管理效率和可用性。以下是创建不同类型的分区表的基本步骤：

1. 范围分区（Range Partitioning）

范围分区是基于列值的范围将数据分布到不同的分区中。例如，按时间字段划分每年的数据。

CREATE TABLE sales (
    sale_id NUMBER PRIMARY KEY,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION sales_q2_2023 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
    -- 更多分区...
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

1.1 按天进行范围分区

在 Oracle 数据库中，要实现每日分区（按天进行范围分区），可以采用间隔分区（Interval Partitioning）的方式。这样当插入新的数据时，系统会自动创建新分区来容纳那些落在预定义分区范围之外的数据。
以下是一个创建每日范围分区的示例：

title:范围分区——按照天来进行分区管理.sql
CREATE TABLE daily_sales (
    sale_id NUMBER PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
    PARTITION sales_20230101 VALUES LESS THAN (TO_DATE('2023-01-02', 'YYYY-MM-DD'))
);

-- 或者使用系统提供的关键字 DEFAULT 字段简化初始分区定义
CREATE TABLE daily_sales (
    sale_id NUMBER PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
    PARTITION sales_first_day VALUES LESS THAN (DATE '2023-01-01') -- 任意一个比实际数据早的日期作为起始点
);

上述 SQL 语句创建了一个名为 daily_sales 的表，并按照 sale_date 字段进行了范围分区，分区间隔设置为一天。第一个分区定义了小于 2023-01-02 的值，这意味着所有 2023 年 1 月 1 日及之前的数据将被放入这个分区。后续每天的数据将自动地在相应日期的基础上创建新的分区。
注意：对于区间分区，Oracle 从指定的第一个分区之后开始自动创建后续分区，直到达到最大分区数或时间边界为止。如果表中已有数据，则需确保初始分区包含这些数据的最早日期。同时，还需考虑表空间配置和分区管理策略，以确保自动分区的顺利进行。

2. 列表分区（List Partitioning）

列表分区基于列值匹配预定义的列表来分配数据。

title:列表分区
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    department VARCHAR2(50),
    hire_date DATE
)
PARTITION BY LIST (department) (
    PARTITION sales_dept VALUES ('Sales'),
    PARTITION hr_dept VALUES ('Human Resources'),
    PARTITION finance_dept VALUES ('Finance'),
    PARTITION others DEFAULT
);

3. 哈希分区（Hash Partitioning）

哈希分区使用散列函数对列值计算散列值，并根据散列结果均匀地分布在指定数量的分区中。

title:哈希分区
CREATE TABLE customer (
    cust_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    address VARCHAR2(200)
)
PARTITION BY HASH (cust_id)
PARTITIONS 4;

4. 组合分区（Composite Partitioning）

组合分区是上述分区类型的结合，如范围 - 列表分区。

title:组合分区
CREATE TABLE order_details (
    order_id NUMBER,
    product_id NUMBER,
    delivery_year NUMBER,
    delivery_month VARCHAR2(10)
)
PARTITION BY RANGE (delivery_year)
SUBPARTITION BY LIST (delivery_month) (
    PARTITION orders_2023 VALUES LESS THAN (2024)
    (
        SUBPARTITION january_2023 VALUES ('JAN'),
        SUBPARTITION february_2023 VALUES ('FEB'),
        -- 其他月份子分区...
        SUBPARTITION december_2023 VALUES ('DEC')
    ),
    -- 其他年份分区...
    PARTITION orders_future VALUES LESS THAN (MAXVALUE)
    (
        -- 对未来的月份设置相应的子分区
    )
);

请注意，在实际应用中，您需要根据业务需求和数据分布特点来设计合适的分区策略。同时，创建或修改分区时可能需要考虑现有数据迁移、维护成本以及索引策略等因素。此外，Oracle 提供了诸如区间范围、引用分区等更多高级分区选项以适应复杂场景。

三、分区数据查询

查询 Oracle 中分区内数据的方式与查询非分区表的数据基本一致，不过可以根据需要指定查询的分区以提高查询效率。以下是一些示例：

查询整个分区表中的数据：
```
SELECT * FROM sales;
```
根据分区键值查询特定分区的数据：
假设 sales 表是按照 sale_date 进行范围分区的，并且有一个分区名为 sales_2023Q1 包含 2023 年第一季度的数据。
```
-- 查询2023年第一季度的所有销售记录
SELECT * FROM sales PARTITION (sales_2023Q1);
```
在 WHERE 子句中利用分区键进行分区剪枝（Partition Pruning）：
Oracle 会自动优化 SQL 查询，如果 WHERE 条件能明确指向某个分区，则只会扫描该分区的数据。
```
-- 查询2023年3月的所有销售记录
SELECT * FROM sales WHERE sale_date BETWEEN DATE '2023-03-01' AND DATE '2023-03-31';
```
在这个例子中，Oracle 将能够识别出只用扫描包含 2023 年 3 月份数据的分区。

查询子分区：
对于复合分区（如范围 - 列表分区），也可以按子分区进行查询。

-- 假设order_details表按年范围分区，每月为子分区
SELECT * FROM order_details PARTITION (orders_2023) SUBPARTITION (january_2023);

通过合理使用分区查询，可以显著减少 I/O 开销，提高查询性能。同时，在设计和编写查询时，应尽量确保查询条件能充分利用分区信息进行优化。

四、索引创建

在 Oracle 数据库中，为分区表创建索引的方式与非分区表基本相同，但还可以根据需要创建本地索引（Local Index）或全局索引（Global Index）。以下是创建这两种索引的示例：

1. 创建全局索引（Global Index）

全局索引是对整个分区表的数据进行索引，并且索引本身也是一个单独的对象。适用于对全表范围内的查询有较高要求的情况。

CREATE INDEX idx_global_sales ON sales (product_id); -- 假设sales是分区表，按product_id创建一个全局索引

2. 创建本地索引（Local Index）

本地索引是在每个分区内部独立创建的索引，每个分区都有自己的索引段。这可以减少索引占用的空间，并且在只查询单个分区时提高查询性能。

-- 在分区表sales上创建基于sale_date字段的本地索引
CREATE INDEX idx_local_sales ON sales (sale_date) LOCAL;

或者，在创建表的同时定义本地索引：

CREATE TABLE sales (
    sale_id NUMBER PRIMARY KEY,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    ...
)
INDEX idx_local_sales (sale_date) LOCAL; -- 在这里同时定义本地索引

选择使用全局索引还是本地索引取决于具体的业务需求和查询模式。一般来说，如果查询通常仅涉及单个或少数几个分区，则本地索引可能更有效；而如果查询经常跨越多个分区，或者需要对全表数据进行排序和聚合操作，则全局索引可能是更好的选择。

Oracle ——Jan 3, 2024

View / Make Comments