Oracle分区表 #

分区优势
├── 性能提升
│   ├── 分区裁剪
│   └── 并行处理
├── 管理简化
│   ├── 独立备份
│   └── 分区维护
├── 可用性
│   └── 分区独立可用
└── 存储
    └── 分区存储不同表空间

分区类型
├── 范围分区（Range）
│   └── 按值范围分区
├── 列表分区（List）
│   └── 按离散值分区
├── 哈希分区（Hash）
│   └── 按哈希值分区
├── 复合分区（Composite）
│   └── 多级分区组合
└── 引用分区（Reference）
    └── 按外键引用分区

-- 按日期范围分区
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER,
    region VARCHAR2(50)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD')),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- 按数值范围分区
CREATE TABLE orders (
    order_id NUMBER,
    order_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (amount) (
    PARTITION p_small VALUES LESS THAN (1000),
    PARTITION p_medium VALUES LESS THAN (10000),
    PARTITION p_large VALUES LESS THAN (100000),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

-- 添加分区
ALTER TABLE sales ADD PARTITION p2026
VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD'));

-- 分裂分区
ALTER TABLE sales SPLIT PARTITION pmax
AT (TO_DATE('2027-01-01', 'YYYY-MM-DD'))
INTO (PARTITION p2026, PARTITION pmax);

-- 合并分区
ALTER TABLE sales MERGE PARTITIONS p2023, p2024
INTO PARTITION p2023_2024;

-- 删除分区
ALTER TABLE sales DROP PARTITION p2023;

-- 截断分区
ALTER TABLE sales TRUNCATE PARTITION p2024;

-- 按地区列表分区
CREATE TABLE customers (
    customer_id NUMBER,
    name VARCHAR2(100),
    region VARCHAR2(50)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES ('Beijing', 'Tianjin'),
    PARTITION p_south VALUES ('Shanghai', 'Guangzhou'),
    PARTITION p_west VALUES ('Chengdu', 'Chongqing'),
    PARTITION p_other VALUES (DEFAULT)
);

-- 添加分区值
ALTER TABLE customers MODIFY PARTITION p_north
ADD VALUES ('Shenyang');

-- 删除分区值
ALTER TABLE customers MODIFY PARTITION p_north
DROP VALUES ('Shenyang');

-- 创建哈希分区表
CREATE TABLE transactions (
    trans_id NUMBER,
    trans_date DATE,
    amount NUMBER
)
PARTITION BY HASH (trans_id)
PARTITIONS 4
STORE IN (ts1, ts2, ts3, ts4);

-- 或指定分区名
CREATE TABLE transactions (
    trans_id NUMBER,
    trans_date DATE,
    amount NUMBER
)
PARTITION BY HASH (trans_id) (
    PARTITION p1,
    PARTITION p2,
    PARTITION p3,
    PARTITION p4
);

-- 范围-列表复合分区
CREATE TABLE sales_data (
    sale_id NUMBER,
    sale_date DATE,
    region VARCHAR2(50),
    amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
    SUBPARTITION sp_north VALUES ('Beijing', 'Tianjin'),
    SUBPARTITION sp_south VALUES ('Shanghai', 'Guangzhou'),
    SUBPARTITION sp_other VALUES (DEFAULT)
) (
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

-- 范围-哈希复合分区
CREATE TABLE orders_data (
    order_id NUMBER,
    order_date DATE,
    customer_id NUMBER,
    amount NUMBER
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 4 (
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

-- 本地索引：每个分区独立索引
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- 本地分区索引
CREATE INDEX idx_sales_local ON sales(sale_id, sale_date)
LOCAL (
    PARTITION p2023,
    PARTITION p2024,
    PARTITION p2025,
    PARTITION pmax
);

-- 全局索引：跨所有分区的索引
CREATE INDEX idx_sales_global ON sales(sale_id) GLOBAL;

-- 全局分区索引
CREATE INDEX idx_sales_global_part ON sales(sale_date)
GLOBAL PARTITION BY RANGE (sale_date) (
    PARTITION pg1 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
    PARTITION pg2 VALUES LESS THAN (MAXVALUE)
);

-- 查询特定分区
SELECT * FROM sales PARTITION(p2024);

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

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

-- 移动分区
ALTER TABLE sales MOVE PARTITION p2024
TABLESPACE users_new;

-- 交换分区
CREATE TABLE sales_temp AS SELECT * FROM sales WHERE 1=0;
ALTER TABLE sales EXCHANGE PARTITION p2024 WITH TABLE sales_temp;

-- 重命名分区
ALTER TABLE sales RENAME PARTITION p2024 TO p_2024;

-- 分区裁剪：只扫描相关分区
-- 查询自动使用分区裁剪
SELECT * FROM sales
WHERE sale_date BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD')
                  AND TO_DATE('2024-12-31', 'YYYY-MM-DD');
-- 只扫描p2024分区

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT * FROM sales WHERE sale_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);