【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
)
ASv_sql VARCHAR2(4000);v_partition_count NUMBER := 0;v_dropped_count NUMBER := 0;v_cutoff_date DATE;
BEGINDBMS_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_valueFROM user_tab_partitionsWHERE table_name = UPPER(p_table_name)AND interval = 'YES'ORDER BY partition_position) LOOPv_partition_count := v_partition_count + 1;-- 解析high_value中的日期DECLAREv_high_date DATE;v_high_value_str VARCHAR2(4000);BEGIN-- 获取分区的上界值SELECT high_value INTO v_high_value_strFROM user_tab_partitionsWHERE 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 THENv_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('  -> 已删除');ELSEDBMS_OUTPUT.PUT_LINE('  -> 保留');END IF;EXCEPTIONWHEN OTHERS THENDBMS_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));EXCEPTIONWHEN OTHERS THENDBMS_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 ALLSELECT 'ORDERS_CONFIRMED', total_amount FROM orders_list_status PARTITION(orders_confirmed)UNION ALLSELECT 'ORDERS_SHIPPED', total_amount FROM orders_list_status PARTITION(orders_shipped)UNION ALLSELECT 'ORDERS_COMPLETED', total_amount FROM orders_list_status PARTITION(orders_completed)UNION ALLSELECT 'ORDERS_CANCELLED', total_amount FROM orders_list_status PARTITION(orders_cancelled)UNION ALLSELECT '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  -- 逗号分隔的值列表
)
ASv_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);
BEGINDBMS_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_valuesFROM user_tab_partitionsWHERE 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 LOOPv_pos := INSTR(v_remaining, ',');IF v_pos > 0 THENv_value := TRIM(SUBSTR(v_remaining, 1, v_pos - 1));v_remaining := SUBSTR(v_remaining, v_pos + 1);ELSEv_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' THENFOR i IN 1..v_values_to_process.COUNT LOOPv_sql := 'ALTER TABLE ' || p_table_name || ' MODIFY PARTITION ' || p_partition_name || ' ADD VALUES (''' || v_values_to_process(i) || ''')';BEGINEXECUTE IMMEDIATE v_sql;DBMS_OUTPUT.PUT_LINE('已添加值: ' || v_values_to_process(i));EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('添加值失败 ' || v_values_to_process(i) || ': ' || SQLERRM);END;END LOOP;ELSIF UPPER(p_action) = 'DROP' THENFOR i IN 1..v_values_to_process.COUNT LOOPv_sql := 'ALTER TABLE ' || p_table_name || ' MODIFY PARTITION ' || p_partition_name || ' DROP VALUES (''' || v_values_to_process(i) || ''')';BEGINEXECUTE IMMEDIATE v_sql;DBMS_OUTPUT.PUT_LINE('已删除值: ' || v_values_to_process(i));EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('删除值失败 ' || v_values_to_process(i) || ': ' || SQLERRM);END;END LOOP;ELSERAISE_APPLICATION_ERROR(-20001, '无效的操作类型: ' || p_action);END IF;-- 显示更新后的分区值SELECT high_value INTO v_current_valuesFROM user_tab_partitionsWHERE table_name = UPPER(p_table_name)AND partition_name = UPPER(p_partition_name);DBMS_OUTPUT.PUT_LINE('更新后分区值: ' || v_current_values);EXCEPTIONWHEN OTHERS THENDBMS_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;-- 批量插入测试数据
DECLAREv_categories NUMBER := 10;v_suppliers NUMBER := 20;v_statuses SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('ACTIVE', 'INACTIVE', 'DISCONTINUED', 'PENDING');
BEGINFOR i IN 1..10000 LOOPINSERT 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 THENCOMMIT;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;-- 插入测试数据
DECLAREv_activity_types SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('LOGIN', 'BROWSE', 'SEARCH', 'PURCHASE', 'LOGOUT', 'DOWNLOAD', 'UPLOAD');v_device_types SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('DESKTOP', 'MOBILE', 'TABLET');
BEGINFOR i IN 1..50000 LOOPINSERT 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 THENCOMMIT;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_rowsFROM user_tab_partitionsWHERE 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
)
ASv_start_time TIMESTAMP;v_end_time TIMESTAMP;v_elapsed_seconds NUMBER;v_result_count NUMBER;v_total_amount NUMBER;
BEGINDBMS_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_amountFROM products_hashWHERE 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_countFROM products_hash pJOIN 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_priceFROM (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) LOOPNULL; -- 只是为了测试执行时间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';EXCEPTIONWHEN OTHERS THENDBMS_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;-- 插入测试数据
DECLAREv_regions SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('NORTH', 'SOUTH', 'EAST', 'WEST');
BEGINFOR i IN 1..20000 LOOPINSERT 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 THENCOMMIT;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
ASv_count NUMBER;v_amount NUMBER;
BEGINDBMS_OUTPUT.PUT_LINE('=== 复合分区查询分析 ===');-- 查询1: 分区消除 - 只访问特定日期范围DBMS_OUTPUT.PUT_LINE('');DBMS_OUTPUT.PUT_LINE('查询1: 日期范围查询（分区消除）');EXPLAIN PLAN FORSELECT COUNT(*), SUM(amount)FROM sales_composite_range_hashWHERE 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 FORSELECT *FROM sales_composite_range_hashWHERE customer_id = 123AND 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_amountFROM sales_composite_range_hashWHERE sale_date >= DATE '2022-01-01'GROUP BY EXTRACT(YEAR FROM sale_date), regionORDER 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_amountFROM (SELECT 'SALES_2023' as partition_name, 'SYS_SUBP101' as subpartition_name, amount FROM sales_composite_range_hash SUBPARTITION(SYS_SUBP101)UNION ALLSELECT 'SALES_2023', 'SYS_SUBP102', amount FROM sales_composite_range_hash SUBPARTITION(SYS_SUBP102)UNION ALLSELECT 'SALES_2023', 'SYS_SUBP103', amount FROM sales_composite_range_hash SUBPARTITION(SYS_SUBP103)UNION ALLSELECT 'SALES_2023', 'SYS_SUBP104', amount FROM sales_composite_range_hash SUBPARTITION(SYS_SUBP104))GROUP BY partition_name, subpartition_nameORDER BY partition_name, subpartition_name) LOOPDBMS_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;

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