Oracle表分区详解

Oracle表分区是一种将大表分解为更小、更易管理的部分的技术，同时保持表的逻辑完整性。分区可以提高查询性能、简化数据管理并优化维护操作。

分区类型

1. 范围分区(Range Partitioning)

按照值的范围进行分区，常用于日期、数字等连续值。

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
    PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY')),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

2. 列表分区(List Partitioning)

基于离散值列表进行分区，适用于有明确分类的数据。

CREATE TABLE employees (
    emp_id NUMBER,
    name VARCHAR2(100),
    location VARCHAR2(50)
)
PARTITION BY LIST (location) (
    PARTITION emp_east VALUES ('New York', 'Boston', 'Washington'),
    PARTITION emp_west VALUES ('San Francisco', 'Los Angeles', 'Seattle'),
    PARTITION emp_central VALUES ('Chicago', 'Dallas', 'Houston'),
    PARTITION emp_other VALUES (DEFAULT)
);

3. 哈希分区(Hash Partitioning)

使用哈希算法均匀分布数据，适用于没有明显分区键的情况。

CREATE TABLE customers (
    cust_id NUMBER,
    name VARCHAR2(100),
    email VARCHAR2(100)
)
PARTITION BY HASH (cust_id)
PARTITIONS 4;

4. 复合分区(Composite Partitioning)

结合两种分区方法，通常是先范围分区再哈希或列表分区。

CREATE TABLE sales_composite (
    sale_id NUMBER,
    sale_date DATE,
    customer_id NUMBER,
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 4 (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
    PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
);

分区维护操作

添加分区

ALTER TABLE sales ADD PARTITION sales_2024 
VALUES LESS THAN (TO_DATE('01-JAN-2025', 'DD-MON-YYYY'));

删除分区

ALTER TABLE sales DROP PARTITION sales_q1;

合并分区

ALTER TABLE sales MERGE PARTITIONS sales_q1, sales_q2 
INTO PARTITION sales_h1;

拆分分区

ALTER TABLE sales SPLIT PARTITION sales_h1 
AT (TO_DATE('01-APR-2023', 'DD-MON-YYYY'))
INTO (PARTITION sales_q1, PARTITION sales_q2);

交换分区

ALTER TABLE sales EXCHANGE PARTITION sales_q1 
WITH TABLE sales_archive;

分区表查询优化

分区裁剪(Partition Pruning)

Oracle自动识别查询中使用的分区键，只访问相关分区。

-- 只访问sales_q2分区
SELECT * FROM sales WHERE sale_date BETWEEN '01-APR-2023' AND '30-JUN-2023';

分区连接(Partition-wise Join)

当连接表的分区方式相同时，Oracle可以并行执行分区间的连接。

最佳实践

1. 选择合适的分区键：经常用于查询条件的列
2. 避免过多分区：通常不超过1000个分区
3. 考虑分区大小：每个分区大小应相对均衡
4. 为MAXVALUE分区预留空间
5. 定期维护分区：添加新分区，归档旧分区

监控分区表

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

-- 查看分区统计信息
SELECT partition_name, num_rows, blocks 
FROM user_tab_partitions 
WHERE table_name = 'SALES';

分区是Oracle中管理大型表的强大功能，合理使用可以显著提高性能和管理效率。