Oracle分区表主键分区索引创建方案及最佳实践咨询

安全阿岳

2026-8-30

3亿行量级Oracle按月分区表主键索引设计

场景说明

计划创建数据量约3亿行的审计分区表，初始建表语句如下：

create table audit
(
    id             number(38,0) not null enable,
    audit_time     timestamp(6),
    description    varchar2(100 byte),
    constraint pk_audit primary key (id)
)
partition by range (audit_time)
    interval(numtoyminterval(1, 'month'))
(
    partition low_p values less than (timestamp' 2010-01-01 00:00:00')
);

create index audit_idx on audit(audit_time) local;

当前未查询到分区表主键索引分区规则的明确说明，存在以下疑问：

主键是否需要创建分区索引？
主键索引必须选择全局分区还是本地分区？
主键索引必须采用哈希分区方式吗？
如何确定主键索引的合理分区数量？

初步拟定的主键索引创建语句如下，需要确认写法合理性：

CREATE INDEX audit_unq
ON audit(id)
GLOBAL PARTITION BY HASH (id)
( PARTITION p1
, PARTITION p2
, PARTITION p3
, PARTITION p4
);

问题解答

核心前置规则

Oracle分区表的主键/唯一约束如果要使用本地分区索引，必须将表的分区键包含在主键/唯一索引的列集合中，否则Oracle会直接报错，这是硬性语法限制。

逐个问题答复

主键是否需要创建分区索引？
3亿行规模的表必须创建。如果主键使用非分区全局索引，只要执行分区维护操作（比如删除过期历史分区、拆分分区），整个全局索引就会完全失效，必须全量重建。3亿行量级的索引重建往往需要数小时，期间会锁表阻塞业务，完全不适合需要定期清理历史数据的按月分区审计表。非分区全局索引仅适用于数据量小、几乎不做分区维护的小型表。
主键索引必须选择全局分区还是本地分区？
没有强制要求，根据主键列是否包含分区键选择：
- 如果可以将分区键audit_time加入主键（即主键调整为(id, audit_time)），优先选择本地分区索引。本地索引和表分区一一对应，分区维护时仅会操作对应分区的索引段，不会影响其他分区，维护成本极低，也是Oracle官方推荐的分区索引方案。
- 如果坚持主键仅保留id列，不符合本地唯一索引的创建规则，只能选择全局分区索引。
主键索引必须采用哈希分区方式吗？
不是强制要求，但对于当前场景（主键是数值型序列ID、分布均匀）是最优选择：
- 范围分区适合存在大量范围查询、需要按区间清理的字段，序列生成的ID几乎不会作为大范围查询条件，也不会按ID区间删除数据，建范围分区全局索引没有实际收益。
- 列表分区仅适合枚举值少的列，完全不适配ID这种高基数字段。
  只有当主键本身带时间属性、业务存在大量按主键范围查询的场景时，才考虑范围分区的全局索引。
如何确定主键索引的合理分区数量？
参考两个核心维度：
- 单分区大小：控制单分区索引大小在50G以内，最大不要超过100G，否则备份、恢复、重建的耗时会过长。3亿行的数值型主键索引总大小约60-80G，分4个哈希分区时单分区大小在15-20G，属于合理区间。
- 并发性能：分区数不要超过数据库服务器的CPU核心数，避免过多分区并行扫描带来的上下文切换开销。OLTP场景下全局哈希分区数建议取2的幂次（2、4、8、16），不要设置为奇数，避免哈希分布不均。

拟定语句合理性判断

你写的4分区哈希全局索引语法本身是正确的，但存在两个关键问题：

单独创建的索引不会自动关联为主键约束，你需要在建主键约束时通过using index子句指定该索引，否则最终只是创建了一个普通唯一索引，不会作为主键的支撑索引。
正确的主键创建写法示例：

alter table audit add constraint pk_audit primary key (id)
using index (
    CREATE INDEX pk_audit ON audit(id)
    GLOBAL PARTITION BY HASH (id)
    ( PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4 )
);

4个分区的数量对于当前3亿行的规模是合理的，如果后续数据量增长到10亿以上，可以再调整为8个分区。

分区表主键索引设计通用最佳实践

优先选择本地索引方案：只要业务允许，将分区键加入主键/唯一键，使用本地分区索引。这种方案维护成本最低，性能最稳定，不会因为分区维护操作导致大面积索引失效。审计类场景下ID本身全局唯一，加入audit_time到主键不会破坏唯一性，完全可以采用该方案。
必须使用全局索引时优先选哈希分区：针对高基数字段的主键，哈希分区可以均匀打散数据，避免索引热点，同时分区维护时仅影响部分索引分区，不会出现全索引失效的问题。
禁止在大分区表上创建非分区全局索引：只要存在分区维护操作，非分区全局索引就会全量失效，重建成本极高，生产环境极易引发故障。
控制全局分区索引的分区数量：OLTP场景下全局分区数控制在2-16个即可，分区过多会导致SQL解析时需要访问更多段，反而拖慢查询性能。
定期检查索引可用性：即使是分区全局索引，部分分区操作仍可能导致个别索引分区失效，需要定期巡检，避免出现无效索引影响业务。

内容的提问来源于stack exchange，提问作者user2625402

Oracle分区表主键分区索引创建方案及最佳实践咨询

场景说明 #

问题解答 #

核心前置规则 #

逐个问题答复 #

拟定语句合理性判断 #