2. 首页 > Oracle教程 > 正文

Oracle教程FG089-分区裁剪

教程整理：风哥教程  |  更新时间：2026-01-06  |  教程分类：Oracle教程  |  文档学习：23

Part02-生产环境规划与建议

2.1 分区裁剪配置

分区裁剪的配置参数：

  • OPTIMIZER_FEATURES_ENABLE：控制优化器特性的启用
  • PARTITION_PRUNING：控制分区裁剪的启用
  • PARALLEL_DEGREE_POLICY：控制并行执行策略
  • DB_FILE_MULTIBLOCK_READ_COUNT：控制多块读取的大小

风哥提示：在生产环境中，应根据系统的硬件配置和 workload 特点选择合适的分区裁剪配置。

2.2 分区裁剪策略

分区裁剪的策略：

  • 使用分区键作为查询条件：在WHERE子句中使用分区键作为查询条件
  • 避免在分区键上使用函数：避免在分区键上使用函数，以免影响分区裁剪
  • 使用绑定变量：合理使用绑定变量，避免因绑定变量导致分区裁剪失效
  • 收集统计信息：定期收集分区表的统计信息，确保优化器能够正确进行分区裁剪
  • 监控分区裁剪：定期监控分区裁剪的效果，及时发现和解决问题
  • 选择合适的分区策略：根据数据特点选择合适的分区策略，提高分区裁剪的效果

更多学习教程公众号风哥教程itpux_com

2.3 分区裁剪注意事项

分区裁剪的注意事项：

  • 分区键选择：选择合适的分区键，确保查询能够有效地进行分区裁剪
  • 查询条件：确保查询条件中包含分区键，以便进行分区裁剪
  • 函数使用：避免在分区键上使用函数，以免影响分区裁剪
  • 绑定变量：注意绑定变量对分区裁剪的影响
  • 统计信息：确保分区表的统计信息准确，以便优化器能够正确进行分区裁剪
  • 执行计划：监控执行计划，确保分区裁剪正常工作
  • 分区维护：定期维护分区表，确保分区结构合理

from oracle:www.itpux.com

Part03-生产环境项目实施方案

在生产环境中实施分区裁剪的实施方案：

  1. 评估数据特点：评估数据的特点，确定是否适合使用分区表。
  2. 选择分区策略：根据数据特点选择合适的分区策略，如范围分区、列表分区、哈希分区等。
  3. 设计分区键：选择合适的分区键，确保查询能够有效地进行分区裁剪。
  4. 创建分区表：创建分区表，设置合理的分区结构。
  5. 测试分区裁剪：测试分区裁剪的效果，确保查询能够正确进行分区裁剪。
  6. 部署实施：在生产环境中部署分区表。
  7. 监控维护：定期监控分区裁剪的效果，及时发现和解决问题。
  8. 优化调整：根据监控结果调整分区结构，优化分区裁剪效果。

Part04-生产案例与实战讲解

4.1 范围分区裁剪

示例：范围分区裁剪

— 创建范围分区表
CREATE TABLE sales_range (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
product_id NUMBER,
quantity NUMBER,
amount NUMBER
)
PARTITION BY RANGE (sale_date) (
PARTITION p2023 QVALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2025 VALUES LESS THAN (TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2026 VALUES LESS THAN (TO_DATE(‘2027-01-01’, ‘YYYY-MM-DD’))
);– 插入测试数据
INSERT INTO sales_range VALUES (1, TO_DATE(‘2023-06-01’, ‘YYYY-MM-DD’), 1001, 10, 1000);INSERT INTO sales_range VALUES (2, TO_DATE(‘2024-06-01’, ‘YYYY-MM-DD’), 1002, 20, 2000);INSERT INTO sales_range VALUES (3, TO_DATE(‘2025-06-01’, ‘YYYY-MM-DD’), 1003, 30, 3000);INSERT INTO sales_range VALUES (4, TO_DATE(‘2026-06-01’, ‘YYYY-MM-DD’), 1004, 40, 4000);– 测试分区裁剪：只访问p2024分区
SELECT * FROM sales_range WHERE sale_date BETWEEN TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’) AND
TO_DATE(‘2024-12-31’, ‘YYYY-MM-DD’);– 测试分区裁剪：访问p2024和p2025分区
SELECT * FROM sales_range WHERE sale_date BETWEEN TO_DATE(‘2024-06-01’, ‘YYYY-MM-DD’) AND
TO_DATE(‘2025-06-30’, ‘YYYY-MM-DD’);– 测试分区裁剪：访问所有分区（没有裁剪）
SELECT * FROM sales_range;

— 测试分区裁剪：只访问p2024分区
Execution Plan
———————————————————-
Plan hash value: 3898747075

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_RANGE | 1 | 41 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | SYS_C001234 | 1 | | 1 (0)| 00:00:01 | 2 | 2 |
—————————————————————————————————-

— 测试分区裁剪：访问p2024和p2025分区
Execution Plan
———————————————————-
Plan hash value: 3898747075

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 2 | 82 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 2 | 82 | 4 (0)| 00:00:01 | 2 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_RANGE | 2 | 82 | 4 (0)| 00:00:01 | 2 | 3 |
|* 3 | INDEX RANGE SCAN | SYS_C001234 | 2 | | 2 (0)| 00:00:01 | 2 | 3 |
—————————————————————————————————-

— 测试分区裁剪：访问所有分区（没有裁剪）
Execution Plan
———————————————————-
Plan hash value: 3898747075

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 4 | 164 | 8 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 4 | 164 | 8 (0)| 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_RANGE | 4 | 164 | 8 (0)| 00:00:01 | 1 | 4 |
|* 3 | INDEX RANGE SCAN | SYS_C001234 | 4 | | 4 (0)| 00:00:01 | 1 | 4 |
—————————————————————————————————-

更多视频教程www.fgedu.net.cn

4.2 列表分区裁剪

示例：列表分区裁剪

— 创建列表分区表
CREATE TABLE sales_list (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
region VARCHAR2(50),
product_id NUMBER,
quantity NUMBER,
amount NUMBER
)
PARTITION BY LIST (region) (
PARTITION p_north VALUES (‘North’, ‘Northeast’),
PARTITION p_south VALUES (‘South’, ‘Southeast’),
PARTITION p_west VALUES (‘West’, ‘Southwest’),
PARTITION p_east VALUES (‘East’, ‘Southeast’)
);– 插入测试数据
INSERT INTO sales_list VALUES (1, TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’), ‘North’, 1001, 10, 1000);INSERT INTO sales_list VALUES (2, TO_DATE(‘2026-01-02’, ‘YYYY-MM-DD’), ‘South’, 1002, 20, 2000);INSERT INTO sales_list VALUES (3, TO_DATE(‘2026-01-03’, ‘YYYY-MM-DD’), ‘West’, 1003, 30, 3000);INSERT INTO sales_list VALUES (4, TO_DATE(‘2026-01-04’, ‘YYYY-MM-DD’), ‘East’, 1004, 40, 4000);– 测试分区裁剪：只访问p_north分区
SELECT * FROM sales_list WHERE region = ‘North’;– 测试分区裁剪：访问p_north和p_south分区
SELECT * FROM sales_list WHERE region IN (‘North’, ‘South’);– 测试分区裁剪：访问所有分区（没有裁剪）
SELECT * FROM sales_list;

— 测试分区裁剪：只访问p_north分区
Execution Plan
———————————————————-
Plan hash value: 1234567890

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 51 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 1 | 51 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_LIST | 1 | 51 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | SYS_C001235 | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
—————————————————————————————————-

— 测试分区裁剪：访问p_north和p_south分区
Execution Plan
———————————————————-
Plan hash value: 1234567890

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 2 | 102 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION LIST ITERATOR | | 2 | 102 | 4 (0)| 00:00:01 | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_LIST | 2 | 102 | 4 (0)| 00:00:01 | 1 | 2 |
|* 3 | INDEX RANGE SCAN | SYS_C001235 | 2 | | 2 (0)| 00:00:01 | 1 | 2 |
—————————————————————————————————-

— 测试分区裁剪：访问所有分区（没有裁剪）
Execution Plan
———————————————————-
Plan hash value: 1234567890

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 4 | 204 | 8 (0)| 00:00:01 | | |
| 1 | PARTITION LIST ALL | | 4 | 204 | 8 (0)| 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_LIST | 4 | 204 | 8 (0)| 00:00:01 | 1 | 4 |
|* 3 | INDEX RANGE SCAN | SYS_C001235 | 4 | | 4 (0)| 00:00:01 | 1 | 4 |
—————————————————————————————————-

学习交流加群风哥微信: itpux-com

4.3 哈希分区裁剪

示例：哈希分区裁剪

— 创建哈希分区表
CREATE TABLE sales_hash (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
product_id NUMBER,
quantity NUMBER,
amount NUMBER
)
PARTITION BY HASH (product_id) (
PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);– 插入测试数据
INSERT INTO sales_hash VALUES (1, TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’), 1001, 10, 1000);INSERT INTO sales_hash VALUES (2, TO_DATE(‘2026-01-02’, ‘YYYY-MM-DD’), 1002, 20, 2000);INSERT INTO sales_hash VALUES (3, TO_DATE(‘2026-01-03’, ‘YYYY-MM-DD’), 1003, 30, 3000);INSERT INTO sales_hash VALUES (4, TO_DATE(‘2026-01-04’, ‘YYYY-MM-DD’), 1004, 40, 4000);– 测试分区裁剪：只访问一个哈希分区
SELECT * FROM sales_hash WHERE product_id = 1001;– 测试分区裁剪：访问多个哈希分区
SELECT * FROM sales_hash WHERE product_id IN (1001, 1002);– 测试分区裁剪：访问所有分区（没有裁剪）
SELECT * FROM sales_hash;

— 测试分区裁剪：只访问一个哈希分区
Execution Plan
———————————————————-
Plan hash value: 9876543210

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 41 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION HASH SINGLE | | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_HASH | 1 | 41 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | SYS_C001236 | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
—————————————————————————————————-

— 测试分区裁剪：访问多个哈希分区
Execution Plan
———————————————————-
Plan hash value: 9876543210

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 2 | 82 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION HASH ITERATOR | | 2 | 82 | 4 (0)| 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_HASH | 2 | 82 | 4 (0)| 00:00:01 | 1 | 4 |
|* 3 | INDEX RANGE SCAN | SYS_C001236 | 2 | | 2 (0)| 00:00:01 | 1 | 4 |
—————————————————————————————————-

— 测试分区裁剪：访问所有分区（没有裁剪）
Execution Plan
———————————————————-
Plan hash value: 9876543210

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 4 | 164 | 8 (0)| 00:00:01 | | |
| 1 | PARTITION HASH ALL | | 4 | 164 | 8 (0)| 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_HASH | 4 | 164 | 8 (0)| 00:00:01 | 1 | 4 |
|* 3 | INDEX RANGE SCAN | SYS_C001236 | 4 | | 4 (0)| 00:00:01 | 1 | 4 |
—————————————————————————————————-

学习交流加群风哥QQ113257174

4.4 复合分区裁剪

示例：复合分区裁剪

— 创建范围-列表复合分区表
CREATE TABLE sales_composite (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
region VARCHAR2(50),
product_id NUMBER,
quantity NUMBER,
amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region) (
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’)) (
SUBPARTITION p2024_north VALUES (‘North’, ‘Northeast’),
SUBPARTITION p2024_south VALUES (‘South’, ‘Southeast’),
SUBPARTITION p2024_west VALUES (‘West’, ‘Southwest’),
SUBPARTITION p2024_east VALUES (‘East’, ‘Southeast’)
),
PARTITION p2025 VALUES LESS THAN (TO_DATE(‘2026-01-01’, ‘YYYY-MM-DD’)) (
SUBPARTITION p2025_north VALUES (‘North’, ‘Northeast’),
SUBPARTITION p2025_south VALUES (‘South’, ‘Southeast’),
SUBPARTITION p2025_west VALUES (‘West’, ‘Southwest’),
SUBPARTITION p2025_east VALUES (‘East’, ‘Southeast’)
)
);– 插入测试数据
INSERT INTO sales_composite VALUES (1, TO_DATE(‘2024-06-01’, ‘YYYY-MM-DD’), ‘North’, 1001, 10, 1000);INSERT INTO sales_composite VALUES (2, TO_DATE(‘2024-06-02’, ‘YYYY-MM-DD’), ‘South’, 1002, 20, 2000);INSERT INTO sales_composite VALUES (3, TO_DATE(‘2025-06-01’, ‘YYYY-MM-DD’), ‘North’, 1003, 30, 3000);INSERT INTO sales_composite VALUES (4, TO_DATE(‘2025-06-02’, ‘YYYY-MM-DD’), ‘South’, 1004, 40, 4000);– 测试分区裁剪：只访问p2024_north子分区
SELECT * FROM sales_composite WHERE sale_date BETWEEN TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2024-12-31’, ‘YYYY-MM-DD’) AND region = ‘North’;– 测试分区裁剪：访问p2024_north和p2024_south子分区
SELECT * FROM sales_composite WHERE sale_date BETWEEN TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2024-12-31’, ‘YYYY-MM-DD’) AND region IN (‘North’, ‘South’);– 测试分区裁剪：访问p2024和p2025的north子分区
SELECT * FROM sales_composite WHERE region = ‘North’;

— 测试分区裁剪：只访问p2024_north子分区
Execution Plan
———————————————————-
Plan hash value: 5678901234

———————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | IN-OUT |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 51 | 2 (0)| 00:00:01 | | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 51 | 2 (0)| 00:00:01 | 1 | 1 | |
| 2 | PARTITION LIST SINGLE | | 1 | 51 | 2 (0)| 00:00:01 | 1 | 1 | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES_COMPOSITE | 1 | 51 | 2 (0)| 00:00:01 | 1 | 1 | |
|* 4 | INDEX RANGE SCAN | SYS_C001237 | 1 | | 1 (0)| 00:00:01 | 1 | 1 | |
———————————————————————————————————-

— 测试分区裁剪：访问p2024_north和p2024_south子分区
Execution Plan
———————————————————-
Plan hash value: 5678901234

———————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | IN-OUT |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 2 | 102 | 4 (0)| 00:00:01 | | | |
| 1 | PARTITION RANGE SINGLE | | 2 | 102 | 4 (0)| 00:00:01 | 1 | 1 | |
| 2 | PARTITION LIST ITERATOR | | 2 | 102 | 4 (0)| 00:00:01 | 1 | 2 | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES_COMPOSITE | 2 | 102 | 4 (0)| 00:00:01 | 1 | 2 | |
|* 4 | INDEX RANGE SCAN | SYS_C001237 | 2 | | 2 (0)| 00:00:01 | 1 | 2 | |
———————————————————————————————————-

— 测试分区裁剪：访问p2024和p2025的north子分区
Execution Plan
———————————————————-
Plan hash value: 5678901234

———————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | IN-OUT |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 2 | 102 | 4 (0)| 00:00:01 | | | |
| 1 | PARTITION RANGE ITERATOR | | 2 | 102 | 4 (0)| 00:00:01 | 1 | 2 | |
| 2 | PARTITION LIST SINGLE | | 2 | 102 | 4 (0)| 00:00:01 | 1 | 1 | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES_COMPOSITE | 2 | 102 | 4 (0)| 00:00:01 | 1 | 1 | |
|* 4 | INDEX RANGE SCAN | SYS_C001237 | 2 | | 2 (0)| 00:00:01 | 1 | 1 | |
———————————————————————————————————-

学习交流加群风哥QQ113257174

Part05-风哥经验总结与分享

风哥提示：在使用分区裁剪时，应注意以下几点：

  • 选择合适的分区键：选择经常用于查询条件的列作为分区键，以提高分区裁剪的效果。
  • 在查询条件中使用分区键：确保查询条件中包含分区键，以便进行分区裁剪。
  • 避免在分区键上使用函数：避免在分区键上使用函数，以免影响分区裁剪。
  • 合理使用绑定变量：注意绑定变量对分区裁剪的影响，避免因绑定变量导致分区裁剪失效。
  • 收集统计信息：定期收集分区表的统计信息，确保优化器能够正确进行分区裁剪。
  • 监控分区裁剪：定期监控分区裁剪的效果，及时发现和解决问题。
  • 选择合适的分区策略：根据数据特点选择合适的分区策略，提高分区裁剪的效果。
  • 合理设计分区结构：合理设计分区结构，确保分区裁剪能够有效地减少数据扫描范围。
  • 测试分区裁剪：在使用分区表前，测试分区裁剪的效果，确保查询能够正确进行分区裁剪。
  • 结合其他优化手段：分区裁剪应与其他优化手段结合使用，如索引优化、SQL重写等。

更多学习教程公众号风哥教程itpux_com

from:风哥.QQ113257174.WX:itpux-com,web: http://www.fgedu.net.cn

风哥提示：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。学习交流加群风哥QQ113257174

生产环境建议：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。

风哥提示：更多学习教程公众号风哥教程itpux_com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息