在Oracle的分区表/索引设计中，经常有这样的需求：分区表中的某个或者某几个分区上创建索引，其他分区不需要索引或者将索引置为unusable，以减少DML操作的IO压力，在Oracle 11g及更早的版本中，无论是Local还是Global index，都不能实现该需求，但是从Oracle 12C开始引进了Partial Global/Local Indexesfor Partitioned Tables，解决了该问题。其实,这个功能在别的数据库早就实现了，比如：PostgreSQL等，Oracle推出的有点。本文将从PartialIndexes创建、对执行计划的影响及维护方面对PartialIndex进行一个简单的介绍。
创建Partial Indexes
通过在表或者分区，分别设置[INDEXING {ON | OFF }]选项，实现对于各分区Index的控制，同时在建索引的时候，加上INDEXINGPARTIAL 关键字，先看如下的例子：
CREATE TABLE "T_OBJ"
( "OWNER"VARCHAR2(128 BYTE),
"OBJECT_NAME" VARCHAR2(128 BYTE),
"SUBOBJECT_NAME" VARCHAR2(128 BYTE),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(23 BYTE),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19 BYTE),
"STATUS" VARCHAR2(7 BYTE),
"TEMPORARY" VARCHAR2(1 BYTE),
"GENERATED" VARCHAR2(1 BYTE),
"SECONDARY" VARCHAR2(1 BYTE),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(128 BYTE),
"SHARING" VARCHAR2(13 BYTE),
"EDITIONABLE" VARCHAR2(1 BYTE),
"ORACLE_MAINTAINED" VARCHAR2(1 BYTE)
) INDEXING OFF
PARTITION BY RANGE (OBJECT_ID)
(PARTITION p1 VALUES LESS THAN (1000) INDEXING ON,
PARTITION p2 VALUES LESS THAN (2000) INDEXING OFF,
PARTITION p3 VALUES LESS THAN (3000) INDEXING ON,
PARTITION p4 VALUES LESS THAN (4000),
PARTITION p5 VALUES LESS THAN (maxvalue));
以上SQL新建一个T_OBJ的表，表级别的Indexing是OFF，表示缺省情况，各分区的Indexing属性是OFF; 在分区级别，分别对分区P1、P2、P3进行了指定，所以这个表的分区Indexing属性如下所示：
select partition_name ,indexing Fromdba_tab_partitions where table_name='T_OBJ'
写入测试数据：insert into t_obj select *From dba_objects;
在T_OBJ上，新建一个Local的分区索引，指定INDEXING PARTIAL CREATE INDEX idx_obj_id on T_OBJ(OBJECT_ID) LOCAL INDEXING PARTIAL;
查看索引IDX_OBJ_ID的Indexing属性及的各分区的状态。
select index_name,indexing From dba_indexeswhere index_name='IDX_OBJ_ID'
备注:非Partial Index是的Indexing属性是FULL。
select index_name,partition_name,status fromdba_ind_partitions where index_name='IDX_OBJ_ID'
通过上述信息，我们清楚的知道，rdbms通过表和其分区的Indexing属性以及索引上的Indexing属性及索引分区的Status与以前的普通索引进行区分。
PartialIndexes对执行计划的影响
采用Partialindex，对于执行计划会有什么影响呢，通过以下几个例子，可以从中看到一些规律。
1. 查询的数据在Indexing=ON的分区时
select *From t_obj where OBJECT_ID=500，此记录在P1分区中，此分区的indexing=on，其执行计划如下：
结论：查询的数据在Indexing=ON的分区时，使用分区索引。
2.查询的数据在Indexing=OFF的分区时
select *From t_obj where OBJECT_ID=1200，此记录在P2分区中，此分区的indexing=off，其执行计划如下：
结论：查询的数据在Indexing=OFF的分区时，分区全扫描。
3.查询的数据跨多个分区，同时存在Indexing=ON与OFF的情况
select *From t_obj where OBJECT_ID between 500 and 3200,此SQL查询多个分区，其执行计划如下：
结论：查询的数据跨多个分区时，数据在Indexing=ON的分区时，使用分区索引；数据在Indexing=OFF的分区时，分区全扫描。
在Global 索引上，使用Partial Index时，执行计划较复杂，可以自行测试。
分区表的indexing属性修改
在分区表（indexing属性已定）建好后，如果我们想启用或者停电某一个分区上的数据的索引功能时，可以通过修改分区的indexing属性来达到这个目的。
例如，启用T_OBJ表P2分区的索引功能alter table T_OBJ modify PARTITION p2 indexing on,查询分区索引状态如下：
Idx_obj_id的p2分区状态已经变成usable，所以这个alterindex .. modify partition .. indexing on的命令，隐含还执行了index partition rebuild操作。
当然，执行alter table T_OBJ modify PARTITION p2 indexing off后，状态又回到unusable。
总之，PartialGlobal/Local Indexes for Partitioned Tables功能对于一些以时间为分区条件，当前活动数据DML操作多，不能包括太多索引，而历史数据查询量大，需要更多的索引的需求，是一个非常好的解决方案。
原创文章，版权归本文作者所有，如需转载或合作，请务必联系我们
欢迎咨询海天起点技术专家
免费热线：800-810-3650 400-810-3650
喜欢本文请长按下方的二维码订阅海天起点
