文章目录
正文
1. 分区表基础概述
1.1 什么是分区表
分区表是Oracle数据库中将大表物理分割成多个较小、更易管理的片段的技术。每个分区可以独立管理，同时对应用程序保持透明。
分区表的核心优势：
- 性能优化：分区消除、并行处理、分区连接
- 管理便利：独立维护、分区交换、在线重定义
- 可用性提升：分区级备份、故障隔离、快速恢复
- 存储优化：数据压缩、存储分层、空间管理
1.2 分区类型总览
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_customer ON sales_range_date (customer_id) LOCAL;
CREATE INDEX idx_sales_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);
COMMIT;
-- 查看分区信息
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
WHERE table_name = 'SALES_RANGE_DATE'
ORDER BY partition_position;
-- 演示分区消除 (Partition Pruning)
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)
);
-- 查看数据分布
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')
);
-- 插入跨多个月的数据，观察自动分区创建
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);
COMMIT;
-- 查看自动创建的分区
SELECT table_name, partition_name, high_value, interval
FROM user_tab_partitions
WHERE table_name = 'SALES_INTERVAL_MONTHLY'
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_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'));
-- 删除过期分区
FOR rec IN (
SELECT partition_name
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;
DECLARE
v_high_date DATE;
v_sql VARCHAR2(1000);
BEGIN
-- 动态获取分区上界日期
EXECUTE IMMEDIATE
'SELECT MAX(sale_date) FROM ' || p_table_name ||
' PARTITION(' || rec.partition_name || ')'
INTO v_high_date;
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('已删除分区: ' || rec.partition_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('处理分区 ' || rec.partition_name || ' 时出错: ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('检查分区数: ' || v_partition_count);
DBMS_OUTPUT.PUT_LINE('删除分区数: ' || v_dropped_count);
END;
/
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');
COMMIT;
-- 查看数据分布
SELECT
partition_name,
high_value,
num_rows
FROM user_tab_partitions
WHERE table_name = 'CUSTOMERS_LIST_REGION'
ORDER BY partition_position;
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)
)
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')
);
-- 创建订单状态统计视图
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'
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)
)
GROUP BY partition_name;
3.2 列表分区值管理
-- 创建列表分区值管理存储过程
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;
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);
-- 执行添加或删除操作
IF UPPER(p_action) = 'ADD' THEN
v_sql := 'ALTER TABLE ' || p_table_name ||
' MODIFY PARTITION ' || p_partition_name ||
' ADD VALUES (''' || p_values || ''')';
ELSIF UPPER(p_action) = 'DROP' THEN
v_sql := 'ALTER TABLE ' || p_table_name ||
' MODIFY PARTITION ' || p_partition_name ||
' DROP VALUES (''' || p_values || ''')';
ELSE
RAISE_APPLICATION_ERROR(-20001, '无效的操作类型: ' || p_action);
END IF;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('操作成功执行');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('操作失败: ' || SQLERRM);
END;
/
-- 测试分区值管理
EXEC manage_list_partition_values('CUSTOMERS_LIST_REGION', 'CUSTOMERS_NORTH', 'ADD', 'NORTH_CENTRAL');
4. 哈希分区 (HASH Partitioning)
4.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_category ON products_hash (category_id) LOCAL;
CREATE INDEX idx_products_supplier ON products_hash (supplier_id) LOCAL;
-- 批量插入测试数据
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO products_hash VALUES (
i,
'Product ' || i,
MOD(i, 10) + 1,
ROUND(DBMS_RANDOM.VALUE(10, 1000), 2),
MOD(i, 20) + 1,
SYSDATE - DBMS_RANDOM.VALUE(0, 365),
CASE MOD(i, 4)
WHEN 0 THEN 'ACTIVE'
WHEN 1 THEN 'INACTIVE'
WHEN 2 THEN 'DISCONTINUED'
ELSE 'PENDING'
END
);
IF MOD(i, 1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
-- 查看哈希分区的数据分布
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
FROM user_tab_partitions
WHERE table_name = 'PRODUCTS_HASH'
ORDER BY partition_name;
4.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;
-- 分析哈希分区的均匀性
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;
5. 复合分区 (Composite Partitioning)
5.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;
5.2 范围-列表复合分区
-- 创建范围-列表复合分区表
CREATE TABLE orders_composite_range_list (
order_id NUMBER,
customer_id NUMBER,
order_date DATE,
total_amount NUMBER(10,2),
order_status VARCHAR2(20),
shipping_region VARCHAR2(20),
payment_method VARCHAR2(20)
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY LIST (shipping_region)
SUBPARTITION TEMPLATE (
SUBPARTITION north_region VALUES ('NORTH', 'NORTHEAST', 'NORTHWEST'),
SUBPARTITION south_region VALUES ('SOUTH', 'SOUTHEAST', 'SOUTHWEST'),
SUBPARTITION east_region VALUES ('EAST', 'CENTRAL_EAST'),
SUBPARTITION west_region VALUES ('WEST', 'CENTRAL_WEST'),
SUBPARTITION other_region VALUES (DEFAULT)
)
(
PARTITION orders_q1_2023 VALUES LESS THAN (DATE '2023-04-01'),
PARTITION orders_q2_2023 VALUES LESS THAN (DATE '2023-07-01'),
PARTITION orders_q3_2023 VALUES LESS THAN (DATE '2023-10-01'),
PARTITION orders_q4_2023 VALUES LESS THAN (DATE '2024-01-01')
);
6. 分区维护操作
6.1 添加和删除分区
-- 添加新的分区
ALTER TABLE sales_range_date
ADD PARTITION sales_2024 VALUES LESS THAN (DATE '2025-01-01');
-- 删除分区
ALTER TABLE sales_range_date
DROP PARTITION sales_2020;
-- 分割分区
ALTER TABLE sales_range_date
SPLIT PARTITION sales_future
AT (DATE '2026-01-01')
INTO (PARTITION sales_2025, PARTITION sales_future);
-- 合并分区
ALTER TABLE sales_range_date
MERGE PARTITIONS sales_2021, sales_2022
INTO PARTITION sales_2021_2022;
6.2 分区交换
-- 创建临时表用于分区交换
CREATE TABLE sales_temp_2023 AS
SELECT * FROM sales_range_date WHERE 1=0;
-- 将分区数据交换到临时表
ALTER TABLE sales_range_date
EXCHANGE PARTITION sales_2023
WITH TABLE sales_temp_2023;
-- 验证交换结果
SELECT COUNT(*) FROM sales_range_date PARTITION(sales_2023);
SELECT COUNT(*) FROM sales_temp_2023;
6.3 在线重定义分区
-- 创建分区表管理工具包
CREATE OR REPLACE PACKAGE partition_maintenance AS
PROCEDURE add_monthly_partitions(
p_table_name VARCHAR2,
p_months_ahead NUMBER DEFAULT 6
);
PROCEDURE drop_old_partitions(
p_table_name VARCHAR2,
p_months_to_keep NUMBER DEFAULT 24
);
PROCEDURE gather_partition_stats(
p_table_name VARCHAR2,
p_partition_name VARCHAR2 DEFAULT NULL
);
END partition_maintenance;
/
CREATE OR REPLACE PACKAGE BODY partition_maintenance AS
PROCEDURE add_monthly_partitions(
p_table_name VARCHAR2,
p_months_ahead NUMBER DEFAULT 6
) AS
v_sql VARCHAR2(4000);
v_partition_name VARCHAR2(128);
v_partition_date DATE;
BEGIN
FOR i IN 1..p_months_ahead LOOP
v_partition_date := ADD_MONTHS(TRUNC(SYSDATE, 'MM'), i);
v_partition_name := p_table_name || '_' ||
TO_CHAR(v_partition_date, 'YYYY_MM');
v_sql := 'ALTER TABLE ' || p_table_name ||
' ADD PARTITION ' || v_partition_name ||
' VALUES LESS THAN (DATE ''' ||
TO_CHAR(ADD_MONTHS(v_partition_date, 1), 'YYYY-MM-DD') ||
''')';
BEGIN
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('已添加分区: ' || v_partition_name);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -14074 THEN -- 分区已存在
RAISE;
END IF;
END;
END LOOP;
END add_monthly_partitions;
PROCEDURE drop_old_partitions(
p_table_name VARCHAR2,
p_months_to_keep NUMBER DEFAULT 24
) AS
v_sql VARCHAR2(4000);
v_cutoff_date DATE;
BEGIN
v_cutoff_date := ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -p_months_to_keep);
FOR rec IN (
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = UPPER(p_table_name)
ORDER BY partition_position
) LOOP
-- 这里需要根据实际分区命名规则来判断
-- 简化示例，实际使用时需要解析分区的high_value
NULL;
END LOOP;
END drop_old_partitions;
PROCEDURE gather_partition_stats(
p_table_name VARCHAR2,
p_partition_name VARCHAR2 DEFAULT NULL
) AS
BEGIN
IF p_partition_name IS NULL THEN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => p_table_name,
granularity => 'ALL'
);
ELSE
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => p_table_name,
partname => p_partition_name,
granularity => 'PARTITION'
);
END IF;
END gather_partition_stats;
END partition_maintenance;
/
7. 分区表性能优化
7.1 分区消除策略
-- 验证分区消除效果
CREATE OR REPLACE PROCEDURE test_partition_pruning AS
BEGIN
DBMS_OUTPUT.PUT_LINE('=== 分区消除测试 ===');
-- 测试1: 单分区访问
EXPLAIN PLAN FOR
SELECT COUNT(*), SUM(amount)
FROM sales_range_date
WHERE sale_date BETWEEN DATE '2023-01-01' AND DATE '2023-12-31';
DBMS_OUTPUT.PUT_LINE('查询1: 单分区访问');
FOR rec IN (SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PARTITION'))) LOOP
DBMS_OUTPUT.PUT_LINE(rec.plan_table_output);
END LOOP;
-- 测试2: 多分区访问
EXPLAIN PLAN FOR
SELECT COUNT(*), SUM(amount)
FROM sales_range_date
WHERE sale_date BETWEEN DATE '2022-06-01' AND DATE '2023-06-30';
DBMS_OUTPUT.PUT_LINE('查询2: 多分区访问');
FOR rec IN (SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PARTITION'))) LOOP
DBMS_OUTPUT.PUT_LINE(rec.plan_table_output);
END LOOP;
END;
/
EXEC test_partition_pruning;
7.2 分区连接优化
-- 创建分区连接优化示例
CREATE OR REPLACE PROCEDURE demo_partition_join AS
v_count NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('=== 分区连接优化演示 ===');
-- 分区连接：基于相同分区键的连接
EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM sales_range_date s1, sales_range_date s2
WHERE s1.sale_date = s2.sale_date
AND s1.customer_id != s2.customer_id
AND s1.sale_date BETWEEN DATE '2023-01-01' AND DATE '2023-12-31';
DBMS_OUTPUT.PUT_LINE('分区连接执行计划:');
FOR rec IN (SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'BASIC +PARTITION'))) LOOP
DBMS_OUTPUT.PUT_LINE(rec.plan_table_output);
END LOOP;
END;
/
EXEC demo_partition_join;
7.3 并行处理优化
-- 设置分区表的并行度
ALTER TABLE sales_range_date PARALLEL 4;
-- 并行查询示例
SELECT /*+ PARALLEL(s, 4) */
EXTRACT(YEAR FROM sale_date) as year,
EXTRACT(MONTH FROM sale_date) as month,
COUNT(*) as transaction_count,
SUM(amount) as total_amount,
AVG(amount) as avg_amount
FROM sales_range_date s
WHERE sale_date >= DATE '2022-01-01'
GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
ORDER BY year, month;
8. 分区表监控和诊断
8.1 分区统计信息
-- 创建分区监控视图
CREATE OR REPLACE VIEW partition_monitoring AS
SELECT
t.table_name,
p.partition_name,
p.partition_position,
p.num_rows,
p.blocks,
p.avg_row_len,
ROUND(p.num_rows * p.avg_row_len / 1024 / 1024, 2) as size_mb,
p.last_analyzed,
CASE
WHEN p.last_analyzed < SYSDATE - 7 THEN 'STALE'
WHEN p.last_analyzed IS NULL THEN 'NEVER'
ELSE 'CURRENT'
END as stats_status
FROM user_tables t
JOIN user_tab_partitions p ON t.table_name = p.table_name
WHERE t.partitioned = 'YES'
ORDER BY t.table_name, p.partition_position;
-- 查看分区监控信息
SELECT * FROM partition_monitoring;
8.2 分区性能监控
-- 创建分区性能监控存储过程
CREATE OR REPLACE PROCEDURE monitor_partition_performance AS
v_sql_id VARCHAR2(13);
v_plan_hash_value NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('=== 分区表性能监控 ===');
-- 查询活跃的分区表相关SQL
FOR rec IN (
SELECT
sql_id,
plan_hash_value,
executions,
elapsed_time / 1000000 as elapsed_seconds,
cpu_time / 1000000 as cpu_seconds,
buffer_gets,
disk_reads,
sql_text
FROM v$sql
WHERE sql_text LIKE '%PARTITION%'
AND parsing_schema_name = USER
AND executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY
) LOOP
DBMS_OUTPUT.PUT_LINE('SQL ID: ' || rec.sql_id);
DBMS_OUTPUT.PUT_LINE('执行次数: ' || rec.executions);
DBMS_OUTPUT.PUT_LINE('平均执行时间: ' || ROUND(rec.elapsed_seconds / rec.executions, 4) || ' 秒');
DBMS_OUTPUT.PUT_LINE('SQL文本: ' || SUBSTR(rec.sql_text, 1, 100));
DBMS_OUTPUT.PUT_LINE('---');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('监控过程出错: ' || SQLERRM);
END;
/
EXEC monitor_partition_performance;
9. 分区表最佳实践
9.1 分区设计原则
-
选择合适的分区键
- 查询频繁使用的列
- 数据分布相对均匀
- 具有时间或逻辑分组特性
-
分区大小控制
- 单个分区大小控制在2GB以内
- 避免过多的小分区
- 考虑并行度和管理复杂性
-
索引策略
- 优先使用本地索引
- 全局索引需要特别注意维护
- 分区键通常不需要单独索引
9.2 维护策略
-- 创建分区维护调度任务
CREATE OR REPLACE PROCEDURE daily_partition_maintenance AS
BEGIN
-- 收集分区统计信息
FOR rec IN (
SELECT table_name, partition_name
FROM user_tab_partitions
WHERE last_analyzed IS NULL
OR last_analyzed < SYSDATE - 7
) LOOP
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => rec.table_name,
partname => rec.partition_name,
granularity => 'PARTITION'
);
END LOOP;
-- 清理过期分区（示例）
partition_maintenance.drop_old_partitions('SALES_RANGE_DATE', 24);
-- 预创建未来分区
partition_maintenance.add_monthly_partitions('SALES_INTERVAL_MONTHLY', 6);
DBMS_OUTPUT.PUT_LINE('分区维护任务完成: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
-- 创建数据库作业（需要DBA权限）
/*
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'DAILY_PARTITION_MAINTENANCE',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN daily_partition_maintenance; END;',
start_date => SYSDATE,
repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
enabled => TRUE,
comments => '每日分区维护任务'
);
END;
/
*/
9.3 故障排除
-- 分区表诊断工具
CREATE OR REPLACE PROCEDURE diagnose_partition_issues(
p_table_name VARCHAR2
) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('=== 分区表诊断: ' || p_table_name || ' ===');
-- 检查分区表基本信息
FOR rec IN (
SELECT partitioning_type, subpartitioning_type, partition_count
FROM user_part_tables
WHERE table_name = UPPER(p_table_name)
) LOOP
DBMS_OUTPUT.PUT_LINE('分区类型: ' || rec.partitioning_type);
DBMS_OUTPUT.PUT_LINE('子分区类型: ' || NVL(rec.subpartitioning_type, '无'));
DBMS_OUTPUT.PUT_LINE('分区数量: ' || rec.partition_count);
END LOOP;
-- 检查数据分布不均
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('数据分布检查:');
FOR rec IN (
SELECT
partition_name,
num_rows,
CASE
WHEN LAG(num_rows) OVER (ORDER BY partition_position) IS NOT NULL THEN
ROUND(ABS(num_rows - LAG(num_rows) OVER (ORDER BY partition_position)) * 100.0 /
GREATEST(num_rows, LAG(num_rows) OVER (ORDER BY partition_position)), 2)
ELSE 0
END as variance_pct
FROM user_tab_partitions
WHERE table_name = UPPER(p_table_name)
ORDER BY partition_position
) LOOP
DBMS_OUTPUT.PUT_LINE(rec.partition_name || ': ' || NVL(rec.num_rows, 0) ||
' 行 (变化: ' || rec.variance_pct || '%)');
END LOOP;
-- 检查统计信息
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('统计信息检查:');
FOR rec IN (
SELECT
COUNT(*) as total_partitions,
COUNT(CASE WHEN last_analyzed IS NULL THEN 1 END) as no_stats,
COUNT(CASE WHEN last_analyzed < SYSDATE - 7 THEN 1 END) as stale_stats
FROM user_tab_partitions
WHERE table_name = UPPER(p_table_name)
) LOOP
DBMS_OUTPUT.PUT_LINE('总分区数: ' || rec.total_partitions);
DBMS_OUTPUT.PUT_LINE('无统计信息: ' || rec.no_stats);
DBMS_OUTPUT.PUT_LINE('统计信息过期: ' || rec.stale_stats);
END LOOP;
END;
/
-- 使用诊断工具
EXEC diagnose_partition_issues('SALES_RANGE_DATE');
10. 总结
分区表是Oracle数据库中处理大型表的重要技术，通过合理的分区设计可以显著提升查询性能、简化数据管理并提高系统可用性。关键要点包括：
- 选择合适的分区策略：根据数据特征和查询模式选择分区类型
- 优化分区键设计：确保查询能够有效利用分区消除
- 合理控制分区大小：平衡性能和管理复杂性
- 建立维护机制：定期收集统计信息、清理过期数据
- 监控分区性能：持续优化查询和分区策略
结语
感谢您的阅读！期待您的一键三连！欢迎指正！
扫一扫
3509
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
