Oracle（65）如何创建和管理分区索引？

创建和管理分区索引（Partitioned Index）是数据库管理中的重要任务，特别是在处理大规模数据集时。以下是如何在Oracle数据库中创建和管理分区索引的详细步骤和示例代码。

创建分区索引

1. 创建分区表

首先，创建一个分区表。假设我们有一个名为sales的表，根据sale_date列进行范围分区。

CREATE TABLE sales (
    sale_id NUMBER PRIMARY KEY,
    product_id NUMBER,
    customer_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p1 VALUES LESS THAN (DATE '2023-01-01'),
    PARTITION p2 VALUES LESS THAN (DATE '2023-07-01'),
    PARTITION p3 VALUES LESS THAN (DATE '2024-01-01')
);

在这个示例中：

• PARTITION BY RANGE (sale_date)表示根据sale_date列进行范围分区。
• PARTITION p1, p2, p3分别定义了三个分区及其范围。

2. 创建本地分区索引

本地分区索引的每个分区与表的分区一一对应。

CREATE INDEX sales_date_idx ON sales (sale_date)
LOCAL (
    PARTITION p1,
    PARTITION p2,
    PARTITION p3
);

在这个示例中：

• LOCAL关键字表示创建本地分区索引，每个表分区都有对应的索引分区。

3. 创建全局分区索引

全局分区索引的分区方式与表的分区方式无关，可以根据不同的列进行分区。

CREATE INDEX sales_amount_idx ON sales (amount)
GLOBAL PARTITION BY RANGE (amount) (
    PARTITION pa VALUES LESS THAN (100),
    PARTITION pb VALUES LESS THAN (500),
    PARTITION pc VALUES LESS THAN (1000)
);

在这个示例中：

• GLOBAL PARTITION BY RANGE (amount)表示根据amount列进行范围分区，与表的分区方式无关。

管理分区索引

1. 重建分区索引

重建分区索引可以优化性能，特别是在大量数据插入或删除后。

ALTER INDEX sales_date_idx REBUILD PARTITION p1;

在这个示例中：

• REBUILD PARTITION p1表示重建索引的p1分区。

2. 合并分区

合并分区可以减少分区数量，简化管理。

ALTER TABLE sales MERGE PARTITIONS p2, p3 INTO PARTITION p_new;

在这个示例中：

• MERGE PARTITIONS p2, p3 INTO PARTITION p_new表示将p2和p3分区合并为一个新的分区p_new。

3. 拆分分区

拆分分区可以细化数据管理，提高查询性能。

ALTER TABLE sales SPLIT PARTITION p1 AT (DATE '2022-07-01') INTO (
    PARTITION p1a,
    PARTITION p1b
);

在这个示例中：

• SPLIT PARTITION p1 AT (DATE '2022-07-01') INTO (PARTITION p1a, PARTITION p1b)表示将p1分区在指定日期拆分为两个新的分区p1a和p1b。

4. 删除分区

删除分区可以清理过期数据，释放存储空间。

ALTER TABLE sales DROP PARTITION p1;

在这个示例中：

• DROP PARTITION p1表示删除p1分区及其数据。

5. 查看分区索引状态

通过查询数据字典视图，可以查看分区索引的状态和详细信息。

SELECT index_name, partition_name, status
FROM user_ind_partitions
WHERE index_name = 'SALES_DATE_IDX';

总结

分区索引（Partitioned Index）通过将索引数据分成多个分区，提高了查询性能和管理效率。分区索引特别适用于处理大规模数据集，能够减少查询扫描的数据量，并提高并行处理能力。分区索引通常与分区表一起使用，但也可以在非分区表上创建。

示例代码总结

创建分区表

CREATE TABLE sales (
    sale_id NUMBER PRIMARY KEY,
    product_id NUMBER,
    customer_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p1 VALUES LESS THAN (DATE '2023-01-01'),
    PARTITION p2 VALUES LESS THAN (DATE '2023-07-01'),
    PARTITION p3 VALUES LESS THAN (DATE '2024-01-01')
);

创建本地分区索引

CREATE INDEX sales_date_idx ON sales (sale_date)
LOCAL (
    PARTITION p1,
    PARTITION p2,
    PARTITION p3
);

创建全局分区索引

CREATE INDEX sales_amount_idx ON sales (amount)
GLOBAL PARTITION BY RANGE (amount) (
    PARTITION pa VALUES LESS THAN (100),
    PARTITION pb VALUES LESS THAN (500),
    PARTITION pc VALUES LESS THAN (1000)
);

重建分区索引

ALTER INDEX sales_date_idx REBUILD PARTITION p1;

合并分区

ALTER TABLE sales MERGE PARTITIONS p2, p3 INTO PARTITION p_new;

拆分分区

ALTER TABLE sales SPLIT PARTITION p1 AT (DATE '2022-07-01') INTO (
    PARTITION p1a,
    PARTITION p1b
);

删除分区

ALTER TABLE sales DROP PARTITION p1;

查看分区索引状态

SELECT index_name, partition_name, status
FROM user_ind_partitions
WHERE index_name = 'SALES_DATE_IDX';