【Oracle】分区表
个人主页：Guiat
归属专栏：Oracle
文章目录
- 1. 分区表基础概述
- 1.1 分区表的概念与优势
- 1.2 分区类型概览
- 1.3 分区表的工作原理
- 2. 范围分区 (RANGE Partitioning)
- 2.1 基础范围分区
- 2.1.1 按日期范围分区
- 2.1.2 按数值范围分区
- 2.2 间隔分区 (INTERVAL Partitioning)
- 2.2.1 自动创建月度分区
- 2.2.2 间隔分区的管理操作
- 3. 列表分区 (LIST Partitioning)
- 3.1 基础列表分区
- 3.1.1 按地区分区
- 3.1.2 按状态分区
- 3.2 列表分区的动态管理
- 3.2.1 分区值管理
- 4. 哈希分区 (HASH Partitioning)
- 4.1 基础哈希分区
- 4.1.1 均匀数据分布
- 4.1.2 多列哈希分区
- 4.2 哈希分区的性能优化
- 4.2.1 并行查询优化
- 5. 复合分区 (Composite Partitioning)
- 5.1 范围-哈希复合分区
- 5.1.1 按日期范围和哈希的复合分区
- 5.1.2 复合分区的查询优化
正文
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;
结语
感谢您的阅读！期待您的一键三连！欢迎指正！
相关文章：
【Oracle】分区表
个人主页：Guiat 归属专栏：Oracle 文章目录 1. 分区表基础概述1.1 分区表的概念与优势1.2 分区类型概览1.3 分区表的工作原理 2. 范围分区 (RANGE Partitioning)2.1 基础范围分区2.1.1 按日期范围分区2.1.2 按数值范围分区 2.2 间隔分区 (INTERVAL Partit…...
CMake控制VS2022项目文件分组
我们可以通过 CMake 控制源文件的组织结构，使它们在 VS 解决方案资源管理器中以“组”（Filter）的形式进行分类展示。 🎯 目标 通过 CMake 脚本将 .cpp、.h 等源文件分组显示在 Visual Studio 2022 的解决方案资源管理器中。 ✅ 支持的方法汇总（共4种） 方法描述是否推荐…...
【开发技术】.Net使用FFmpeg视频特定帧上绘制内容
目录 一、目的 二、解决方案 2.1 什么是FFmpeg 2.2 FFmpeg主要功能 2.3 使用Xabe.FFmpeg调用FFmpeg功能 2.4 使用 FFmpeg 的 drawbox 滤镜来绘制 ROI 三、总结 一、目的 当前市场上有很多目标检测智能识别的相关算法，当前调用一个医疗行业的AI识别算法后返回…...
RNN避坑指南：从数学推导到LSTM/GRU工业级部署实战流程
本文较长，建议点赞收藏，以免遗失。更多AI大模型应用开发学习视频及资料，尽在聚客AI学院。 本文全面剖析RNN核心原理，深入讲解梯度消失/爆炸问题，并通过LSTM/GRU结构实现解决方案，提供时间序列预测和文本生成…...
【碎碎念】宝可梦 Mesh GO : 基于MESH网络的口袋妖怪 宝可梦GO游戏自组网系统
目录 游戏说明《宝可梦 Mesh GO》 —— 局域宝可梦探索Pokmon GO 类游戏核心理念应用场景Mesh 特性 宝可梦玩法融合设计游戏构想要素1. 地图探索（基于物理空间 广播范围）2. 野生宝可梦生成与广播3. 对战系统4. 道具与通信5. 延伸玩法 安全性设计 技术选…...
精益数据分析（97/126）：邮件营销与用户参与度的关键指标优化指南
精益数据分析（97/126）：邮件营销与用户参与度的关键指标优化指南 在数字化营销时代，邮件列表效度、用户参与度和网站性能等指标往往决定着创业公司的增长成败。今天，我们将深入解析邮件打开率、网站可用性、页面参与时…...
是否存在路径（FIFOBB算法）
题目描述 一个具有 n 个顶点e条边的无向图，该图顶点的编号依次为0到n-1且不存在顶点与自身相连的边。请使用FIFOBB算法编写程序，确定是否存在从顶点 source到顶点 destination的路径。 输入 第一行两个整数，分别表示n 和 e 的值（1…...
中关于正整数输入的校验规则)
Element Plus 表单(el-form)中关于正整数输入的校验规则
目录 1 单个正整数输入1.1 模板1.2 校验规则 2 两个正整数输入（联动）2.1 模板2.2 校验规则2.3 CSS 1 单个正整数输入 1.1 模板 <el-formref"formRef":model"formData":rules"formRules"label-width"150px"…...
学习STC51单片机32（芯片为STC89C52RCRC）OLED显示屏2
每日一言 今天的每一份坚持，都是在为未来积攒底气。 案例：OLED显示一个A 这边观察到一个点，怎么雪花了就是都是乱七八糟的占满了屏幕。。 解释 ： 如果代码里信号切换太快（比如 SDA 刚变，SCL 立刻变&#…...
大数据学习（132）-HIve数据分析
🍋🍋大数据学习🍋🍋 🔥系列专栏： 👑哲学语录: 用力所能及，改变世界。 💖如果觉得博主的文章还不错的话，请点赞👍收藏⭐️留言Ǵ…...
3-11单元格区域边界定位(End属性)学习笔记
返回一个Range 对象，只读。该对象代表包含源区域的区域上端下端左端右端的最后一个单元格。等同于按键 End 向上键(End(xlUp))、End向下键(End(xlDown))、End向左键(End(xlToLeft)End向右键(End(xlToRight)) 注意：它移动的位置必须是相连的有内容的单元格…...
Rapidio门铃消息FIFO溢出机制
关于RapidIO门铃消息FIFO的溢出机制及其与中断抖动的关系，以下是深入解析： 门铃FIFO溢出的本质 在RapidIO系统中，门铃消息FIFO是硬件控制器内部的缓冲区，用于临时存储接收到的门铃消息（Doorbell Message）。…...
蓝桥杯3498 01串的熵
问题描述 对于一个长度为 23333333的 01 串, 如果其信息熵为 11625907.5798， 且 0 出现次数比 1 少, 那么这个 01 串中 0 出现了多少次? #include<iostream> #include<cmath> using namespace std;int n 23333333;int main() {//枚举 0 出现的次数//因…...
什么是Ansible Jinja2
理解 Ansible Jinja2 模板 Ansible 是一款功能强大的开源自动化工具，可让您无缝地管理和配置系统。Ansible 的一大亮点是它使用 Jinja2 模板，允许您根据变量数据动态生成文件、配置设置和脚本。本文将向您介绍 Ansible 中的 Jinja2 模板，并通…...
.Net Framework 4/C# 关键字（非常用，持续更新...）
一、is 关键字 is 关键字用于检查对象是否于给定类型兼容，如果兼容将返回 true，如果不兼容则返回 false，在进行类型转换前，可以先使用 is 关键字判断对象是否与指定类型兼容，如果兼容才进行转换，这样的转换是安全的。 例如有：首先创建一个字符串对象，然后将字符串对象隐…...
优选算法第十二讲：队列 + 宽搜 优先级队列
优选算法第十二讲：队列 宽搜 && 优先级队列 1.N叉树的层序遍历2.二叉树的锯齿型层序遍历3.二叉树最大宽度4.在每个树行中找最大值5.优先级队列 -- 最后一块石头的重量6.数据流中的第K大元素7.前K个高频单词8.数据流的中位数 1.N叉树的层序遍历 2.二叉树的锯…...
项目部署到Linux上时遇到的错误（Redis，MySQL，无法正确连接，地址占用问题）
Redis无法正确连接 在运行jar包时出现了这样的错误 查询得知问题核心在于Redis连接失败，具体原因是客户端发送了密码认证请求，但Redis服务器未设置密码 1.为Redis设置密码（匹配客户端配置） 步骤： 1）.修…...
智能分布式爬虫的数据处理流水线优化：基于深度强化学习的数据质量控制
在数字化浪潮席卷全球的今天，数据已成为企业和研究机构的核心资产。智能分布式爬虫作为高效的数据采集工具，在大规模数据获取中发挥着关键作用。然而，传统的数据处理流水线在面对复杂多变的网络环境和海量异构数据时，常出现数据质…...
OPenCV CUDA模块图像处理-----对图像执行 均值漂移滤波（Mean Shift Filtering）函数meanShiftFiltering()
操作系统：ubuntu22.04 OpenCV版本：OpenCV4.9 IDE:Visual Studio Code 编程语言：C11 算法描述 在 GPU 上对图像执行 均值漂移滤波（Mean Shift Filtering），用于图像分割或平滑处理。 该函数将输入图像中的…...
Java多线程实现之Thread类深度解析
Java多线程实现之Thread类深度解析 一、多线程基础概念1.1 什么是线程1.2 多线程的优势1.3 Java多线程模型 二、Thread类的基本结构与构造函数2.1 Thread类的继承关系2.2 构造函数 三、创建和启动线程3.1 继承Thread类创建线程3.2 实现Runnable接口创建线程 四、Thread类的核心…...
AI书签管理工具开发全记录（十九）：嵌入资源处理
1.前言 📝 在上一篇文章中，我们完成了书签的导入导出功能。本篇文章我们研究如何处理嵌入资源，方便后续将资源打包到一个可执行文件中。 2.embed介绍 🎯 Go 1.16 引入了革命性的 embed 包，彻底改变了静态资源管理的…...
AspectJ 在 Android 中的完整使用指南
一、环境配置（Gradle 7.0 适配） 1. 项目级 build.gradle // 注意：沪江插件已停更，推荐官方兼容方案 buildscript {dependencies {classpath org.aspectj:aspectjtools:1.9.9.1 // AspectJ 工具} } 2. 模块级 build.gradle plu…...
分布式增量爬虫实现方案
之前我们在讨论的是分布式爬虫如何实现增量爬取。增量爬虫的目标是只爬取新产生或发生变化的页面，避免重复抓取，以节省资源和时间。 在分布式环境下，增量爬虫的实现需要考虑多个爬虫节点之间的协调和去重。 另一种思路：将增量判…...
Maven 概述、安装、配置、仓库、私服详解
目录 1、Maven 概述 1.1 Maven 的定义 1.2 Maven 解决的问题 1.3 Maven 的核心特性与优势 2、Maven 安装 2.1 下载 Maven 2.2 安装配置 Maven 2.3 测试安装 2.4 修改 Maven 本地仓库的默认路径 3、Maven 配置 3.1 配置本地仓库 3.2 配置 JDK 3.3 IDEA 配置本地 Ma…...
Mac下Android Studio扫描根目录卡死问题记录
环境信息 操作系统: macOS 15.5 (Apple M2芯片)Android Studio版本: Meerkat Feature Drop | 2024.3.2 Patch 1 (Build #AI-243.26053.27.2432.13536105, 2025年5月22日构建) 问题现象 在项目开发过程中，提示一个依赖外部头文件的cpp源文件需要同步，点…...
DeepSeek 技术赋能无人农场协同作业：用 AI 重构农田管理 “神经网”
目录 一、引言二、DeepSeek 技术大揭秘2.1 核心架构解析2.2 关键技术剖析 三、智能农业无人农场协同作业现状3.1 发展现状概述3.2 协同作业模式介绍 四、DeepSeek 的 “农场奇妙游”4.1 数据处理与分析4.2 作物生长监测与预测4.3 病虫害防治4.4 农机协同作业调度 五、实际案例大…...
dify打造数据可视化图表
一、概述 在日常工作和学习中，我们经常需要和数据打交道。无论是分析报告、项目展示，还是简单的数据洞察，一个清晰直观的图表，往往能胜过千言万语。 一款能让数据可视化变得超级简单的 MCP Server，由蚂蚁集团 AntV 团队…...
使用 Streamlit 构建支持主流大模型与 Ollama 的轻量级统一平台
🎯 使用 Streamlit 构建支持主流大模型与 Ollama 的轻量级统一平台 📌 项目背景 随着大语言模型（LLM）的广泛应用，开发者常面临多个挑战： 各大模型（OpenAI、Claude、Gemini、Ollama）接口风格不统一；缺乏一个统一平台进行模型调用与测试；本地模型 Ollama 的集成与前…...
Unity | AmplifyShaderEditor插件基础（第七集：平面波动shader）
目录 一、👋🏻前言 二、😈sinx波动的基本原理 三、😈波动起来 1.sinx节点介绍 2.vertexPosition 3.集成Vector3 a.节点Append b.连起来 4.波动起来 a.波动的原理 b.时间节点 c.sinx的处理 四、🌊波动优化…...
企业如何增强终端安全？
在数字化转型加速的今天，企业的业务运行越来越依赖于终端设备。从员工的笔记本电脑、智能手机，到工厂里的物联网设备、智能传感器，这些终端构成了企业与外部世界连接的 “神经末梢”。然而，随着远程办公的常态化和设备接入的爆炸式…...