【Oracle】分区表

个人主页：Guiat
归属专栏：Oracle

正文

1. 分区表基础概述

分区表是Oracle数据库中将大表物理分割成多个较小、更易管理的片段的技术。每个分区可以独立管理，同时对应用程序保持透明。

1.1 分区表的概念与优势

1.2 分区类型概览

1.3 分区表的工作原理

2. 范围分区 (RANGE Partitioning)

2.1 基础范围分区

2.1.1 按日期范围分区

-- 创建按日期范围分区的销售表
CREATE TABLE sales_range_date (
    sale_id NUMBER,
    customer_id NUMBER,
    product_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2),
    quantity NUMBER,
    sales_rep_id NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2020 VALUES LESS THAN (DATE '2021-01-01'),
    PARTITION sales_2021 VALUES LESS THAN (DATE '2022-01-01'),
    PARTITION sales_2022 VALUES LESS THAN (DATE '2023-01-01'),
    PARTITION sales_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

-- 创建分区表的索引
CREATE INDEX idx_sales_range_customer ON sales_range_date (customer_id) LOCAL;
CREATE INDEX idx_sales_range_product ON sales_range_date (product_id) LOCAL;

-- 插入测试数据
INSERT INTO sales_range_date VALUES (1, 1001, 2001, DATE '2020-03-15', 1500.00, 3, 501);
INSERT INTO sales_range_date VALUES (2, 1002, 2002, DATE '2021-06-20', 2300.50, 5, 502);
INSERT INTO sales_range_date VALUES (3, 1003, 2003, DATE '2022-09-10', 890.75, 2, 503);
INSERT INTO sales_range_date VALUES (4, 1004, 2004, DATE '2023-12-05', 3200.00, 8, 504);
INSERT INTO sales_range_date VALUES (5, 1005, 2005, DATE '2024-02-14', 1750.25, 4, 505);

COMMIT;

-- 查看分区信息
SELECT table_name, partition_name, high_value, num_rows, blocks
FROM user_tab_partitions 
WHERE table_name = 'SALES_RANGE_DATE'
ORDER BY partition_position;

-- 演示分区消除
EXPLAIN PLAN FOR
SELECT * FROM sales_range_date 
WHERE sale_date BETWEEN DATE '2022-01-01' AND DATE '2022-12-31';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2.1.2 按数值范围分区

-- 创建按员工ID范围分区的员工表
CREATE TABLE employees_range_id (
    employee_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    hire_date DATE,
    salary NUMBER(8,2),
    department_id NUMBER
)
PARTITION BY RANGE (employee_id) (
    PARTITION emp_1_1000 VALUES LESS THAN (1001),
    PARTITION emp_1001_2000 VALUES LESS THAN (2001),
    PARTITION emp_2001_3000 VALUES LESS THAN (3001),
    PARTITION emp_3001_4000 VALUES LESS THAN (4001),
    PARTITION emp_others VALUES LESS THAN (MAXVALUE)
);

-- 插入测试数据
INSERT INTO employees_range_id VALUES (500, 'John', 'Doe', 'john.doe@company.com', SYSDATE, 5000, 10);
INSERT INTO employees_range_id VALUES (1500, 'Jane', 'Smith', 'jane.smith@company.com', SYSDATE, 6000, 20);
INSERT INTO employees_range_id VALUES (2500, 'Bob', 'Johnson', 'bob.johnson@company.com', SYSDATE, 5500, 30);
INSERT INTO employees_range_id VALUES (3500, 'Alice', 'Brown', 'alice.brown@company.com', SYSDATE, 7000, 40);
INSERT INTO employees_range_id VALUES (5000, 'Mike', 'Wilson', 'mike.wilson@company.com', SYSDATE, 8000, 50);

COMMIT;

-- 查看数据分布
SELECT 'emp_1_1000' as partition_name, COUNT(*) as row_count FROM employees_range_id PARTITION(emp_1_1000)
UNION ALL
SELECT 'emp_1001_2000', COUNT(*) FROM employees_range_id PARTITION(emp_1001_2000)
UNION ALL
SELECT 'emp_2001_3000', COUNT(*) FROM employees_range_id PARTITION(emp_2001_3000)
UNION ALL
SELECT 'emp_3001_4000', COUNT(*) FROM employees_range_id PARTITION(emp_3001_4000)
UNION ALL
SELECT 'emp_others', COUNT(*) FROM employees_range_id PARTITION(emp_others);

2.2 间隔分区 (INTERVAL Partitioning)

2.2.1 自动创建月度分区

-- 创建间隔分区表（按月自动分区）
CREATE TABLE sales_interval_monthly (
    sale_id NUMBER,
    customer_id NUMBER,
    product_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2),
    quantity NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
    PARTITION sales_initial VALUES LESS THAN (DATE '2023-01-01')
);

-- 创建本地索引
CREATE INDEX idx_sales_interval_customer ON sales_interval_monthly (customer_id) LOCAL;

-- 插入跨多个月的数据，观察自动分区创建
INSERT INTO sales_interval_monthly VALUES (1, 1001, 2001, DATE '2022-12-15', 1500.00, 3);
INSERT INTO sales_interval_monthly VALUES (2, 1002, 2002, DATE '2023-01-20', 2300.50, 5);
INSERT INTO sales_interval_monthly VALUES (3, 1003, 2003, DATE '2023-02-10', 890.75, 2);
INSERT INTO sales_interval_monthly VALUES (4, 1004, 2004, DATE '2023-03-05', 3200.00, 8);
INSERT INTO sales_interval_monthly VALUES (5, 1005, 2005, DATE '2023-04-14', 1750.25, 4);
INSERT INTO sales_interval_monthly VALUES (6, 1006, 2006, DATE '2023-05-22', 2100.00, 6);

COMMIT;

-- 查看自动创建的分区
SELECT table_name, partition_name, high_value, interval
FROM user_tab_partitions 
WHERE table_name = 'SALES_INTERVAL_MONTHLY'
ORDER BY partition_position;

-- 创建间隔分区表（按天自动分区）
CREATE TABLE transaction_log_daily (
    transaction_id NUMBER,
    user_id NUMBER,
    transaction_type VARCHAR2(20),
    transaction_date DATE,
    amount NUMBER(12,2),
    description VARCHAR2(200)
)
PARTITION BY RANGE (transaction_date)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
    PARTITION trans_initial VALUES LESS THAN (DATE '2024-01-01')
);

-- 插入跨多天的数据
INSERT INTO transaction_log_daily VALUES (1, 1001, 'PURCHASE', DATE '2023-12-30', 150.00, 'Online purchase');
INSERT INTO transaction_log_daily VALUES (2, 1002, 'REFUND', DATE '2024-01-01', -50.00, 'Product return');
INSERT INTO transaction_log_daily VALUES (3, 1003, 'PURCHASE', DATE '2024-01-02', 300.00, 'Store purchase');
INSERT INTO transaction_log_daily VALUES (4, 1004, 'TRANSFER', DATE '2024-01-03', 1000.00, 'Account transfer');

COMMIT;

-- 查看每日分区
SELECT partition_name, high_value, num_rows
FROM user_tab_partitions 
WHERE table_name = 'TRANSACTION_LOG_DAILY'
ORDER BY partition_position;

2.2.2 间隔分区的管理操作

-- 创建间隔分区管理存储过程
CREATE OR REPLACE PROCEDURE manage_interval_partitions(
    p_table_name IN VARCHAR2,
    p_keep_months IN NUMBER DEFAULT 12
)
AS
    v_sql VARCHAR2(4000);
    v_partition_count NUMBER := 0;
    v_dropped_count NUMBER := 0;
    v_cutoff_date DATE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== 间隔分区管理 ===');
    DBMS_OUTPUT.PUT_LINE('表名: ' || p_table_name);
    DBMS_OUTPUT.PUT_LINE('保留月数: ' || p_keep_months);
    
    -- 计算截止日期
    v_cutoff_date := ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -p_keep_months);
    DBMS_OUTPUT.PUT_LINE('删除截止日期: ' || TO_CHAR(v_cutoff_date, 'YYYY-MM-DD'));
    DBMS_OUTPUT.PUT_LINE('');
    
    -- 查询需要删除的分区
    FOR rec IN (
        SELECT partition_name, high_value
        FROM user_tab_partitions
        WHERE table_name = UPPER(p_table_name)
          AND interval = 'YES'
        ORDER BY partition_position
    ) LOOP
        v_partition_count := v_partition_count + 1;
        
        -- 解析high_value中的日期
        DECLARE
            v_high_date DATE;
            v_high_value_str VARCHAR2(4000);
        BEGIN
            -- 获取分区的上界值
            SELECT high_value INTO v_high_value_str
            FROM user_tab_partitions
            WHERE table_name = UPPER(p_table_name)
              AND partition_name = rec.partition_name;
            
            -- 执行动态SQL获取日期值
            EXECUTE IMMEDIATE 'SELECT ' || v_high_value_str || ' FROM dual' INTO v_high_date;
            
            DBMS_OUTPUT.PUT_LINE('分区: ' || rec.partition_name || 
                               ', 上界: ' || TO_CHAR(v_high_date, 'YYYY-MM-DD'));
            
            -- 如果分区太旧，删除它
            IF v_high_date <= v_cutoff_date THEN
                v_sql := 'ALTER TABLE ' || p_table_name || ' DROP PARTITION ' || rec.partition_name;
                EXECUTE IMMEDIATE v_sql;
                v_dropped_count := v_dropped_count + 1;
                DBMS_OUTPUT.PUT_LINE('  -> 已删除');
            ELSE
                DBMS_OUTPUT.PUT_LINE('  -> 保留');
            END IF;
            
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('  -> 处理错误: ' || SQLERRM);
        END;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('=== 管理完成 ===');
    DBMS_OUTPUT.PUT_LINE('检查分区数: ' || v_partition_count);
    DBMS_OUTPUT.PUT_LINE('删除分区数: ' || v_dropped_count);
    DBMS_OUTPUT.PUT_LINE('保留分区数: ' || (v_partition_count - v_dropped_count));
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('管理过程出错: ' || SQLERRM);
END;
/

-- 测试分区管理
EXEC manage_interval_partitions('SALES_INTERVAL_MONTHLY', 6);

3. 列表分区 (LIST Partitioning)

3.1 基础列表分区

3.1.1 按地区分区

-- 创建按地区列表分区的客户表
CREATE TABLE customers_list_region (
    customer_id NUMBER,
    customer_name VARCHAR2(100),
    email VARCHAR2(100),
    phone VARCHAR2(20),
    region VARCHAR2(20),
    country VARCHAR2(50),
    registration_date DATE,
    status VARCHAR2(20)
)
PARTITION BY LIST (region) (
    PARTITION customers_north VALUES ('NORTH', 'NORTHEAST', 'NORTHWEST'),
    PARTITION customers_south VALUES ('SOUTH', 'SOUTHEAST', 'SOUTHWEST'),
    PARTITION customers_east VALUES ('EAST', 'CENTRAL_EAST'),
    PARTITION customers_west VALUES ('WEST', 'CENTRAL_WEST'),
    PARTITION customers_international VALUES ('INTERNATIONAL', 'OVERSEAS'),
    PARTITION customers_default VALUES (DEFAULT)
);

-- 插入测试数据
INSERT INTO customers_list_region VALUES (1, 'ABC Corp', 'contact@abc.com', '555-0001', 'NORTH', 'USA', SYSDATE, 'ACTIVE');
INSERT INTO customers_list_region VALUES (2, 'XYZ Ltd', 'info@xyz.com', '555-0002', 'SOUTH', 'USA', SYSDATE, 'ACTIVE');
INSERT INTO customers_list_region VALUES (3, 'Global Inc', 'sales@global.com', '555-0003', 'INTERNATIONAL', 'UK', SYSDATE, 'ACTIVE');
INSERT INTO customers_list_region VALUES (4, 'Local Store', 'hello@local.com', '555-0004', 'EAST', 'USA', SYSDATE, 'INACTIVE');
INSERT INTO customers_list_region VALUES (5, 'Unknown Co', 'contact@unknown.com', '555-0005', 'OTHER', 'CANADA', SYSDATE, 'PENDING');

COMMIT;

-- 查看数据分布
SELECT 
    p.partition_name,
    p.high_value,
    NVL(s.num_rows, 0) as row_count
FROM user_tab_partitions p
LEFT JOIN user_tab_statistics s ON p.table_name = s.table_name AND p.partition_name = s.partition_name
WHERE p.table_name = 'CUSTOMERS_LIST_REGION'
ORDER BY p.partition_position;

-- 查询特定分区的数据
SELECT * FROM customers_list_region PARTITION(customers_north);
SELECT * FROM customers_list_region PARTITION(customers_default);

3.1.2 按状态分区

-- 创建按订单状态分区的订单表
CREATE TABLE orders_list_status (
    order_id NUMBER,
    customer_id NUMBER,
    order_date DATE,
    total_amount NUMBER(10,2),
    order_status VARCHAR2(20),
    payment_method VARCHAR2(20),
    shipping_address VARCHAR2(200)
)
PARTITION BY LIST (order_status) (
    PARTITION orders_pending VALUES ('PENDING', 'SUBMITTED', 'PROCESSING'),
    PARTITION orders_confirmed VALUES ('CONFIRMED', 'PAID', 'PREPARING'),
    PARTITION orders_shipped VALUES ('SHIPPED', 'IN_TRANSIT', 'OUT_FOR_DELIVERY'),
    PARTITION orders_completed VALUES ('DELIVERED', 'COMPLETED'),
    PARTITION orders_cancelled VALUES ('CANCELLED', 'REFUNDED', 'RETURNED'),
    PARTITION orders_other VALUES (DEFAULT)
);

-- 插入测试数据
INSERT INTO orders_list_status VALUES (1001, 1, SYSDATE-5, 150.00, 'PENDING', 'CREDIT_CARD', '123 Main St');
INSERT INTO orders_list_status VALUES (1002, 2, SYSDATE-4, 250.50, 'CONFIRMED', 'PAYPAL', '456 Oak Ave');
INSERT INTO orders_list_status VALUES (1003, 3, SYSDATE-3, 89.99, 'SHIPPED', 'DEBIT_CARD', '789 Pine Rd');
INSERT INTO orders_list_status VALUES (1004, 4, SYSDATE-2, 320.00, 'DELIVERED', 'CASH', '321 Elm St');
INSERT INTO orders_list_status VALUES (1005, 5, SYSDATE-1, 175.25, 'CANCELLED', 'CREDIT_CARD', '654 Maple Dr');
INSERT INTO orders_list_status VALUES (1006, 1, SYSDATE, 99.99, 'UNKNOWN', 'BITCOIN', '987 Cedar Ln');

COMMIT;

-- 创建订单状态统计视图
CREATE OR REPLACE VIEW order_status_summary AS
SELECT 
    CASE 
        WHEN partition_name = 'ORDERS_PENDING' THEN 'Pending Orders'
        WHEN partition_name = 'ORDERS_CONFIRMED' THEN 'Confirmed Orders'
        WHEN partition_name = 'ORDERS_SHIPPED' THEN 'Shipped Orders'
        WHEN partition_name = 'ORDERS_COMPLETED' THEN 'Completed Orders'
        WHEN partition_name = 'ORDERS_CANCELLED' THEN 'Cancelled Orders'
        ELSE 'Other Status'
    END as status_category,
    COUNT(*) as order_count,
    SUM(total_amount) as total_value
FROM (
    SELECT 'ORDERS_PENDING' as partition_name, total_amount FROM orders_list_status PARTITION(orders_pending)
    UNION ALL
    SELECT 'ORDERS_CONFIRMED', total_amount FROM orders_list_status PARTITION(orders_confirmed)
    UNION ALL
    SELECT 'ORDERS_SHIPPED', total_amount FROM orders_list_status PARTITION(orders_shipped)
    UNION ALL
    SELECT 'ORDERS_COMPLETED', total_amount FROM orders_list_status PARTITION(orders_completed)
    UNION ALL
    SELECT 'ORDERS_CANCELLED', total_amount FROM orders_list_status PARTITION(orders_cancelled)
    UNION ALL
    SELECT 'ORDERS_OTHER', total_amount FROM orders_list_status PARTITION(orders_other)
)
GROUP BY partition_name
ORDER BY order_count DESC;

-- 查看订单状态汇总
SELECT * FROM order_status_summary;

3.2 列表分区的动态管理

3.2.1 分区值管理

-- 创建列表分区管理存储过程
CREATE OR REPLACE PROCEDURE manage_list_partition_values(
    p_table_name IN VARCHAR2,
    p_partition_name IN VARCHAR2,
    p_action IN VARCHAR2, -- 'ADD' or 'DROP'
    p_values IN VARCHAR2  -- 逗号分隔的值列表
)
AS
    v_sql VARCHAR2(4000);
    v_current_values CLOB;
    TYPE value_array IS TABLE OF VARCHAR2(100);
    v_values_to_process value_array;
    v_value VARCHAR2(100);
    v_pos NUMBER;
    v_remaining VARCHAR2(4000);
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== 列表分区值管理 ===');
    DBMS_OUTPUT.PUT_LINE('表名: ' || p_table_name);
    DBMS_OUTPUT.PUT_LINE('分区名: ' || p_partition_name);
    DBMS_OUTPUT.PUT_LINE('操作: ' || p_action);
    DBMS_OUTPUT.PUT_LINE('值: ' || p_values);
    
    -- 获取当前分区值
    SELECT high_value INTO v_current_values
    FROM user_tab_partitions
    WHERE table_name = UPPER(p_table_name)
      AND partition_name = UPPER(p_partition_name);
    
    DBMS_OUTPUT.PUT_LINE('当前分区值: ' || v_current_values);
    
    -- 解析输入的值列表
    v_remaining := p_values;
    v_values_to_process := value_array();
    
    WHILE LENGTH(v_remaining) > 0 LOOP
        v_pos := INSTR(v_remaining, ',');
        IF v_pos > 0 THEN
            v_value := TRIM(SUBSTR(v_remaining, 1, v_pos - 1));
            v_remaining := SUBSTR(v_remaining, v_pos + 1);
        ELSE
            v_value := TRIM(v_remaining);
            v_remaining := '';
        END IF;
        
        v_values_to_process.EXTEND;
        v_values_to_process(v_values_to_process.COUNT) := v_value;
    END LOOP;
    
    -- 执行操作
    IF UPPER(p_action) = 'ADD' THEN
        FOR i IN 1..v_values_to_process.COUNT LOOP
            v_sql := 'ALTER TABLE ' || p_table_name || 
                     ' MODIFY PARTITION ' || p_partition_name || 
                     ' ADD VALUES (''' || v_values_to_process(i) || ''')';
            
            BEGIN
                EXECUTE IMMEDIATE v_sql;
                DBMS_OUTPUT.PUT_LINE('已添加值: ' || v_values_to_process(i));
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('添加值失败 ' || v_values_to_process(i) || ': ' || SQLERRM);
            END;
        END LOOP;
        
    ELSIF UPPER(p_action) = 'DROP' THEN
        FOR i IN 1..v_values_to_process.COUNT LOOP
            v_sql := 'ALTER TABLE ' || p_table_name || 
                     ' MODIFY PARTITION ' || p_partition_name || 
                     ' DROP VALUES (''' || v_values_to_process(i) || ''')';
            
            BEGIN
                EXECUTE IMMEDIATE v_sql;
                DBMS_OUTPUT.PUT_LINE('已删除值: ' || v_values_to_process(i));
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('删除值失败 ' || v_values_to_process(i) || ': ' || SQLERRM);
            END;
        END LOOP;
    ELSE
        RAISE_APPLICATION_ERROR(-20001, '无效的操作类型: ' || p_action);
    END IF;
    
    -- 显示更新后的分区值
    SELECT high_value INTO v_current_values
    FROM user_tab_partitions
    WHERE table_name = UPPER(p_table_name)
      AND partition_name = UPPER(p_partition_name);
    
    DBMS_OUTPUT.PUT_LINE('更新后分区值: ' || v_current_values);
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('操作失败: ' || SQLERRM);
END;
/

-- 测试分区值管理
-- 为customers_north分区添加新的地区值
EXEC manage_list_partition_values('CUSTOMERS_LIST_REGION', 'CUSTOMERS_NORTH', 'ADD', 'NORTH_CENTRAL,UPPER_NORTH');

-- 查看更新后的分区信息
SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'CUSTOMERS_LIST_REGION'
  AND partition_name = 'CUSTOMERS_NORTH';

4. 哈希分区 (HASH Partitioning)

4.1 基础哈希分区

4.1.1 均匀数据分布

-- 创建哈希分区表用于均匀分布数据
CREATE TABLE products_hash (
    product_id NUMBER,
    product_name VARCHAR2(100),
    category_id NUMBER,
    price NUMBER(10,2),
    supplier_id NUMBER,
    created_date DATE,
    status VARCHAR2(20)
)
PARTITION BY HASH (product_id)
PARTITIONS 8;

-- 创建本地索引
CREATE INDEX idx_products_hash_category ON products_hash (category_id) LOCAL;
CREATE INDEX idx_products_hash_supplier ON products_hash (supplier_id) LOCAL;

-- 批量插入测试数据
DECLARE
    v_categories NUMBER := 10;
    v_suppliers NUMBER := 20;
    v_statuses SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('ACTIVE', 'INACTIVE', 'DISCONTINUED', 'PENDING');
BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO products_hash VALUES (
            i,
            'Product ' || i,
            MOD(i, v_categories) + 1,
            ROUND(DBMS_RANDOM.VALUE(10, 1000), 2),
            MOD(i, v_suppliers) + 1,
            SYSDATE - DBMS_RANDOM.VALUE(0, 365),
            v_statuses(MOD(i, 4) + 1)
        );
        
        -- 每1000条提交一次
        IF MOD(i, 1000) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    COMMIT;
END;
/

-- 查看哈希分区的数据分布
SELECT 
    partition_name,
    num_rows,
    blocks,
    avg_row_len
FROM user_tab_partitions
WHERE table_name = 'PRODUCTS_HASH'
ORDER BY partition_name;

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PRODUCTS_HASH');

-- 再次查看分布（收集统计信息后）
SELECT 
    partition_name,
    num_rows,
    ROUND(num_rows * 100.0 / SUM(num_rows) OVER(), 2) as percentage,
    blocks
FROM user_tab_partitions
WHERE table_name = 'PRODUCTS_HASH'
ORDER BY partition_name;

4.1.2 多列哈希分区

-- 创建基于多列的哈希分区表
CREATE TABLE user_activities_hash (
    user_id NUMBER,
    activity_date DATE,
    activity_type VARCHAR2(50),
    session_id VARCHAR2(100),
    duration_minutes NUMBER,
    page_views NUMBER,
    device_type VARCHAR2(20)
)
PARTITION BY HASH (user_id, activity_date)
PARTITIONS 16;

-- 插入测试数据
DECLARE
    v_activity_types SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
        'LOGIN', 'BROWSE', 'SEARCH', 'PURCHASE', 'LOGOUT', 'DOWNLOAD', 'UPLOAD'
    );
    v_device_types SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
        'DESKTOP', 'MOBILE', 'TABLET'
    );
BEGIN
    FOR i IN 1..50000 LOOP
        INSERT INTO user_activities_hash VALUES (
            TRUNC(DBMS_RANDOM.VALUE(1, 5000)),
            SYSDATE - DBMS_RANDOM.VALUE(0, 30),
            v_activity_types(TRUNC(DBMS_RANDOM.VALUE(1, 8))),
            'SESSION_' || LPAD(i, 8, '0'),
            TRUNC(DBMS_RANDOM.VALUE(1, 120)),
            TRUNC(DBMS_RANDOM.VALUE(1, 50)),
            v_device_types(TRUNC(DBMS_RANDOM.VALUE(1, 4)))
        );
        
        IF MOD(i, 5000) = 0 THEN
            COMMIT;
            DBMS_OUTPUT.PUT_LINE('已插入 ' || i || ' 条记录');
        END IF;
    END LOOP;
    COMMIT;
END;
/

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'USER_ACTIVITIES_HASH');

-- 分析哈希分区的均匀性
SELECT 
    partition_name,
    num_rows,
    ROUND(num_rows * 100.0 / SUM(num_rows) OVER(), 2) as percentage,
    blocks,
    avg_row_len
FROM user_tab_partitions
WHERE table_name = 'USER_ACTIVITIES_HASH'
ORDER BY num_rows DESC;

-- 计算分布的标准差（衡量均匀性）
WITH partition_stats AS (
    SELECT num_rows
    FROM user_tab_partitions
    WHERE table_name = 'USER_ACTIVITIES_HASH'
)
SELECT 
    ROUND(AVG(num_rows), 2) as avg_rows_per_partition,
    ROUND(STDDEV(num_rows), 2) as stddev_rows,
    ROUND(STDDEV(num_rows) / AVG(num_rows) * 100, 2) as coefficient_of_variation
FROM partition_stats;

4.2 哈希分区的性能优化

4.2.1 并行查询优化

-- 创建并行查询测试存储过程
CREATE OR REPLACE PROCEDURE test_hash_partition_performance(
    p_parallel_degree IN NUMBER DEFAULT 4
)
AS
    v_start_time TIMESTAMP;
    v_end_time TIMESTAMP;
    v_elapsed_seconds NUMBER;
    v_result_count NUMBER;
    v_total_amount NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== 哈希分区性能测试 ===');
    DBMS_OUTPUT.PUT_LINE('并行度: ' || p_parallel_degree);
    
    -- 设置并行度
    EXECUTE IMMEDIATE 'ALTER TABLE products_hash PARALLEL ' || p_parallel_degree;
    
    -- 测试1: 全表扫描聚合查询
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('测试1: 全表聚合查询');
    v_start_time := SYSTIMESTAMP;
    
    SELECT COUNT(*), SUM(price)
    INTO v_result_count, v_total_amount
    FROM products_hash
    WHERE status = 'ACTIVE';
    
    v_end_time := SYSTIMESTAMP;
    v_elapsed_seconds := EXTRACT(SECOND FROM (v_end_time - v_start_time));
    
    DBMS_OUTPUT.PUT_LINE('结果: ' || v_result_count || ' 行, 总金额: ' || ROUND(v_total_amount, 2));
    DBMS_OUTPUT.PUT_LINE('执行时间: ' || v_elapsed_seconds || ' 秒');
    
    -- 测试2: 分区连接查询
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('测试2: 分区连接查询');
    v_start_time := SYSTIMESTAMP;
    
    SELECT COUNT(*)
    INTO v_result_count
    FROM products_hash p
    JOIN user_activities_hash u ON MOD(p.product_id, 1000) = MOD(u.user_id, 1000)
    WHERE p.status = 'ACTIVE'
      AND u.activity_type = 'PURCHASE';
    
    v_end_time := SYSTIMESTAMP;
    v_elapsed_seconds := EXTRACT(SECOND FROM (v_end_time - v_start_time));
    
    DBMS_OUTPUT.PUT_LINE('连接结果: ' || v_result_count || ' 行');
    DBMS_OUTPUT.PUT_LINE('执行时间: ' || v_elapsed_seconds || ' 秒');
    
    -- 测试3: 分区级别的统计
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('测试3: 分区级别统计');
    v_start_time := SYSTIMESTAMP;
    
    FOR rec IN (
        SELECT 
            'SYS_P' || ROWNUM as partition_name,
            COUNT(*) as row_count,
            AVG(price) as avg_price,
            MAX(price) as max_price
        FROM (
            SELECT price FROM products_hash PARTITION(SYS_P81)
            UNION ALL SELECT price FROM products_hash PARTITION(SYS_P82)
            UNION ALL SELECT price FROM products_hash PARTITION(SYS_P83)
            UNION ALL SELECT price FROM products_hash PARTITION(SYS_P84)
        )
        GROUP BY 'SYS_P' || ROWNUM
    ) LOOP
        NULL; -- 只是为了测试执行时间
    END LOOP;
    
    v_end_time := SYSTIMESTAMP;
    v_elapsed_seconds := EXTRACT(SECOND FROM (v_end_time - v_start_time));
    
    DBMS_OUTPUT.PUT_LINE('分区统计执行时间: ' || v_elapsed_seconds || ' 秒');
    
    -- 重置并行度
    EXECUTE IMMEDIATE 'ALTER TABLE products_hash NOPARALLEL';
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('测试过程出错: ' || SQLERRM);
        EXECUTE IMMEDIATE 'ALTER TABLE products_hash NOPARALLEL';
END;
/

-- 执行性能测试
EXEC test_hash_partition_performance(2);
EXEC test_hash_partition_performance(4);

5. 复合分区 (Composite Partitioning)

5.1 范围-哈希复合分区

5.1.1 按日期范围和哈希的复合分区

-- 创建范围-哈希复合分区表
CREATE TABLE sales_composite_range_hash (
    sale_id NUMBER,
    customer_id NUMBER,
    product_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2),
    quantity NUMBER,
    sales_rep_id NUMBER,
    region VARCHAR2(20)
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 4
(
    PARTITION sales_2022 VALUES LESS THAN (DATE '2023-01-01'),
    PARTITION sales_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION sales_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

-- 查看复合分区结构
SELECT 
    partition_name,
    subpartition_name,
    high_value,
    subpartition_position
FROM user_tab_subpartitions
WHERE table_name = 'SALES_COMPOSITE_RANGE_HASH'
ORDER BY partition_name, subpartition_position;

-- 插入测试数据
DECLARE
    v_regions SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('NORTH', 'SOUTH', 'EAST', 'WEST');
BEGIN
    FOR i IN 1..20000 LOOP
        INSERT INTO sales_composite_range_hash VALUES (
            i,
            TRUNC(DBMS_RANDOM.VALUE(1, 1000)),
            TRUNC(DBMS_RANDOM.VALUE(1, 500)),
            DATE '2022-01-01' + DBMS_RANDOM.VALUE(0, 1095), -- 3年范围
            ROUND(DBMS_RANDOM.VALUE(10, 5000), 2),
            TRUNC(DBMS_RANDOM.VALUE(1, 20)),
            TRUNC(DBMS_RANDOM.VALUE(1, 50)),
            v_regions(TRUNC(DBMS_RANDOM.VALUE(1, 5)))
        );
        
        IF MOD(i, 2000) = 0 THEN
            COMMIT;
            DBMS_OUTPUT.PUT_LINE('已插入 ' || i || ' 条记录');
        END IF;
    END LOOP;
    COMMIT;
END;
/

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SALES_COMPOSITE_RANGE_HASH');

-- 分析数据分布
SELECT 
    partition_name,
    subpartition_name,
    num_rows,
    blocks
FROM user_tab_subpartitions
WHERE table_name = 'SALES_COMPOSITE_RANGE_HASH'
  AND num_rows > 0
ORDER BY partition_name, subpartition_name;

5.1.2 复合分区的查询优化

-- 创建复合分区查询分析存储过程
CREATE OR REPLACE PROCEDURE analyze_composite_partition_queries
AS
    v_count NUMBER;
    v_amount NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== 复合分区查询分析 ===');
    
    -- 查询1: 分区消除 - 只访问特定日期范围
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('查询1: 日期范围查询（分区消除）');
    
    EXPLAIN PLAN FOR
    SELECT COUNT(*), SUM(amount)
    FROM sales_composite_range_hash
    WHERE sale_date BETWEEN DATE '2023-01-01' AND DATE '2023-12-31';
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PARTITION'));
    
    -- 查询2: 子分区消除 - 特定客户和日期
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('查询2: 客户和日期查询（子分区消除）');
    
    EXPLAIN PLAN FOR
    SELECT *
    FROM sales_composite_range_hash
    WHERE customer_id = 123
      AND sale_date BETWEEN DATE '2023-06-01' AND DATE '2023-06-30';
    
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PARTITION'));
    
    -- 查询3: 跨分区聚合
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('查询3: 跨分区聚合查询');
    
    SELECT 
        EXTRACT(YEAR FROM sale_date) as sale_year,
        region,
        COUNT(*) as transaction_count,
        SUM(amount) as total_amount,
        AVG(amount) as avg_amount
    FROM sales_composite_range_hash
    WHERE sale_date >= DATE '2022-01-01'
    GROUP BY EXTRACT(YEAR FROM sale_date), region
    ORDER BY sale_year, region;
    
    -- 查询4: 分区级别的并行处理
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('查询4: 分区级别统计');
    
    FOR rec IN (
        SELECT 
            partition_name,
            subpartition_name,
            COUNT(*) as row_count,
            SUM(amount) as total_amount
        FROM (
            SELECT 'SALES_2023' as partition_name, 'SYS_SUBP101' as subpartition_name, amount 
            FROM sales_composite_range_hash SUBPARTITION(SYS_SUBP101)
            UNION ALL
            SELECT 'SALES_2023', 'SYS_SUBP102', amount 
            FROM sales_composite_range_hash SUBPARTITION(SYS_SUBP102)
            UNION ALL
            SELECT 'SALES_2023', 'SYS_SUBP103', amount 
            FROM sales_composite_range_hash SUBPARTITION(SYS_SUBP103)
            UNION ALL
            SELECT 'SALES_2023', 'SYS_SUBP104', amount 
            FROM sales_composite_range_hash SUBPARTITION(SYS_SUBP104)
        )
        GROUP BY partition_name, subpartition_name
        ORDER BY partition_name, subpartition_name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.partition_name || '.' || rec.subpartition_name || 
                           ': ' || rec.row_count || ' 行, 总额: $' || ROUND(rec.total_amount, 2));
    END LOOP;
    
END;
/

-- 执行复合分区分析
EXEC analyze_composite_partition_queries;

结语
感谢您的阅读！期待您的一键三连！欢迎指正！