Oracle分区表+本地索引：查询性能飙升的核心优化方案（附完整测试案例）

技术分享 3个月前 (12-03) 0 999+

在Oracle数据库运维中，面对海量数据时的查询性能瓶颈是高频痛点。而Oracle分区技术作为企业版的核心增值组件（独立收费），通过将大表按规则拆分到多个物理分区，实现"分而治之"的存储与查询策略，能在特定场景下带来数量级的性能提升。本文将通过完整的实验案例，带大家深入理解分区表与本地索引的创建、使用及性能优势。

一、实验背景与环境说明

数据库版本：Oracle（企业版，需启用分区组件）
测试用户：eygle（拥有DBA权限，用于创建表、索引及查询数据字典）
测试目标：验证分区表+本地索引 vs 非分区表的查询性能差异
数据来源：从系统字典表dba_objects提取6227条有效数据（created < '2008-01-01'）

二、分区表创建：按时间范围拆分大表

分区表的核心是分区键的选择，本文采用最常用的RANGE（范围分区），按CREATED字段（创建时间）将表拆分为2007年前、2007年两个分区。

1. 分区表创建SQL

CONNECT eygle/eygle CREATE TABLE dbobjs (   OBJECT_ID    NUMBER NOT NULL,   OBJECT_NAME  VARCHAR2(128),   CREATED      DATE  NOT NULL  -- 分区键：按时间范围分区 ) PARTITION BY RANGE (CREATED) (   PARTITION dbobjs_06 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),  -- 2007年前数据   PARTITION dbobjs_07 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY'))   -- 2007年数据 );

2. 分区表验证

创建完成后，通过数据字典dba_segments可查看分区表的物理存储情况：

COL segment_name FOR A20 COL PARTITION_NAME FOR A20 SELECT segment_name, partition_name, tablespace_name FROM dba_segments WHERE segment_name = 'DBOBJS';

执行结果：

SEGMENT_NAME	PARTITION_NAME	TABLESPACE_NAME
DBOBJS	DBOBJS_06	EYGLE
DBOBJS	DBOBJS_07	EYGLE

结论：分区表已成功拆分为两个独立的物理段，分别存储不同时间范围的数据。

三、本地索引创建：与分区表"绑定"的优化利器

本地索引（Local Index）是分区表的最佳搭档，其分区规则与表完全一致（一一对应），查询时能自动"定位"到目标分区，避免全表扫描。

1. 本地索引创建（指定分区表空间）

-- 不同分区的索引可存储在不同表空间，实现I/O负载均衡 CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL (   PARTITION dbobjs_06 TABLESPACE users,   PARTITION dbobjs_07 TABLESPACE users );  -- 简化语法：统一指定表空间（适用于所有分区） CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL (   PARTITION dbobjs_06 TABLESPACE users,   PARTITION dbobjs_07 TABLESPACE users ) TABLESPACE users;

2. 本地索引验证

SELECT segment_name, partition_name, tablespace_name FROM dba_segments WHERE segment_name = 'DBOBJS_IDX';

执行结果：

SEGMENT_NAME	PARTITION_NAME	TABLESPACE_NAME
DBOBJS_IDX	DBOBJS_06	USERS
DBOBJS_IDX	DBOBJS_07	USERS

结论：本地索引自动按表分区规则创建对应分区，与表分区形成"一对一"映射。

四、数据插入与分区分布验证

向分区表插入测试数据，并验证数据在各分区的分布情况：

-- 插入6227条数据（来自dba_objects） INSERT INTO dbobjs SELECT object_id, object_name, created FROM dba_objects  WHERE created < TO_DATE('01/01/2008','dd/mm/yyyy')    AND object_id IS NOT NULL;  COMMIT;  -- 验证各分区数据量 SELECT COUNT(*) FROM dbobjs PARTITION (DBOBJS_06);  -- 2007年前：6154条 SELECT COUNT(*) FROM dbobjs PARTITION (DBOBJS_07);  -- 2007年：73条

结论：数据按分区键规则自动分发到对应分区，大部分数据集中在DBOBJS_06分区。

五、性能对比测试：分区表 vs 非分区表

通过autotrace工具查看执行计划和统计信息，重点对比逻辑读（consistent gets） （逻辑读越少，性能越好）。

1. 测试场景1：统计2007年前数据（分区裁剪生效）

分区表查询：

SET AUTOTRACE ON SELECT COUNT(DISTINCT(object_name))  FROM dbobjs  WHERE created < TO_DATE('01/01/2007','dd/mm/yyyy');

执行结果（关键指标）：

逻辑读（consistent gets）：101
执行计划：INDEX RANGE SCAN（仅扫描DBOBJS_06分区索引）

非分区表查询：

先创建非分区表及普通索引：

CREATE TABLE dbobjs2 (   object_id    NUMBER NOT NULL,   object_name  VARCHAR2(128),   created      DATE  NOT NULL );  CREATE INDEX dbobjs_idx2 ON dbobjs2 (created);  -- 插入相同数据 INSERT INTO dbobjs2 SELECT object_id, object_name, created FROM dba_objects  WHERE created < TO_DATE('01/01/2008','dd/mm/yyyy')    AND object_id IS NOT NULL;  COMMIT;  -- 执行相同查询 SELECT COUNT(DISTINCT(object_name))  FROM dbobjs2  WHERE created < TO_DATE('01/01/2007','dd/mm/yyyy');

执行结果（关键指标）：

逻辑读（consistent gets）：2670
执行计划：INDEX RANGE SCAN（扫描整个索引）

2. 测试场景2：统计全量数据（无分区裁剪）

分区表查询：

SELECT COUNT(*)  FROM dbobjs  WHERE created < TO_DATE('01/01/2008','dd/mm/yyyy');

逻辑读：25
执行计划：PARTITION RANGE (ALL)（扫描所有分区索引）

非分区表查询：

SELECT COUNT(*)  FROM dbobjs2  WHERE created < TO_DATE('01/01/2008','dd/mm/yyyy');

逻辑读：约2600（与场景1接近）

性能对比总结表

查询场景	分区表（本地索引）	非分区表（普通索引）	性能提升倍数
统计2007年前数据（distinct）	101次逻辑读	2670次逻辑读	26倍+
统计全量数据（count）	25次逻辑读	2600次逻辑读	104倍+

核心结论：

分区表通过分区裁剪（仅扫描目标分区），在范围查询场景下性能提升极其显著；
即使扫描全部分区，分区表的逻辑读仍远低于非分区表（因分区索引更小、I/O效率更高）；
本地索引与分区表的"一对一"映射，确保查询时无需跨分区扫描，进一步降低开销。

六、分区扩展：本地索引的自动维护特性

当业务需要新增分区时，本地索引会自动同步创建对应分区，无需手动维护，极大降低运维成本：

-- 新增2008年分区（VALUES LESS THAN '2009-01-01'） ALTER TABLE dbobjs ADD PARTITION dbobjs_08 VALUES LESS THAN (TO_DATE('01/01/2009', 'DD/MM/YYYY'));  -- 验证索引分区是否自动创建 SELECT segment_name, partition_name, tablespace_name FROM dba_segments WHERE segment_name = 'DBOBJS_IDX';