【INDEX】Oracle分区索引技术详解

来源：这里教程网时间：2026-03-03 17:30:28 作者：

环境准备

创建分区表

--创建表结构CREATE TABLE sf2021(  sf_id              int        NOT NULL,  sf_name             varchar2(40)           NOT NULL,  sf_address             VARCHAR2(20)       NOT NULL,  insert_time            DATE                    DEFAULT SYSDATE NOT NULL  )PARTITION BY RANGE (insert_time)( PARTITION p202102 VALUES LESS THAN (to_date('202102','yyyymm')), PARTITION p202103 VALUES LESS THAN (to_date('202103','yyyymm')), PARTITION p202104 VALUES LESS THAN (to_date('202104','yyyymm')), PARTITION p202105 VALUES LESS THAN (to_date('202105','yyyymm')), PARTITION p202106 VALUES LESS THAN (to_date('202106','yyyymm')), PARTITION p202107 VALUES LESS THAN (to_date('202107','yyyymm')), PARTITION p202108 VALUES LESS THAN (to_date('202108','yyyymm')), PARTITION pmax VALUES LESS THAN (maxvalue)); --创建插入程序create or replace procedure proc_sf2021_insert(tablecount int)asbegin  for i in 1..tablecount loop   insert into mytest.sf2021select trunc(DBMS_RANDOM.VALUE(100000,10000000000)),DECODE(DBMS_RANDOM.STRING(0, 1),'A','张','B','周','C','李','D','赵','E','安','F','王小','G','杨','杨') || DECODE(DBMS_RANDOM.STRING(0, 1),'A','娜','B','娟','C','梅','D','美','E','鹏','F','爱国','中华'),'山东省'|| DECODE(DBMS_RANDOM.STRING(0, 1),'A','济南市','B','德州市','C','青岛市','D','潍坊市','E','淄博市','F','滨州市','G','临沂市','H','烟台市','L','日照市','M','东营市','N','菏泽市','聊城市'),to_date('2021/'||trunc(dbms_random.value(1,8))||'/'||trunc(dbms_random.value(1,28)),'yyyy/mm/dd') from dual;  end loop;  commit;end;/--插入一万条数据exec proc_sf2021_insert(10000);--查看分区分布col insert_time for a20select to_char(insert_time,'yyyy/mm') insert_time,count(*) from sf2021 group by to_char(insert_time,'yyyy/mm');

本地分区索引

本地分区索引，只适用于分区表分区键

--分区键，创建本地分区索引create index sf2021_idx_id on sf2021(insert_time) local;--查看分区状态   --索引及分区类型可参考 dba_part_indexesSQL> select index_name,PARTITION_NAME,STATUS from DBA_IND_PARTITIONS where index_name='SF2021_IDX_ID';INDEX_NAME               PARTITION_NAME              STATUS------------------------------ ------------------------------ --------SF2021_IDX_ID               P202102                  USABLESF2021_IDX_ID               P202103                  USABLESF2021_IDX_ID               P202104                  USABLESF2021_IDX_ID               P202105                  USABLESF2021_IDX_ID               P202106                  USABLESF2021_IDX_ID               P202107                  USABLESF2021_IDX_ID               P202108                  USABLESF2021_IDX_ID               PMAX                  USABLE8 rows selected.--创建全局hash分区create index sf2021_idx_hash on sf2021(sf_id) global partition by hash(sf_id) partitions 6;

全局分区索引

全局分区索引可分为范围和散列.

--创建全局hash分区create index sf2021_idx_hash on sf2021(sf_id) global partition by hash(sf_id) partitions 6;--查看SQL> select index_name,PARTITION_NAME,STATUS from DBA_IND_PARTITIONS where index_name='SF2021_IDX_HASH';INDEX_NAME               PARTITION_NAME              STATUS------------------------------ ------------------------------ --------SF2021_IDX_HASH            SYS_P61                  USABLESF2021_IDX_HASH            SYS_P62                  USABLESF2021_IDX_HASH            SYS_P63                  USABLESF2021_IDX_HASH            SYS_P64                  USABLESF2021_IDX_HASH            SYS_P65                  USABLESF2021_IDX_HASH            SYS_P66                  USABLE6 rows selected.

注意，全局分区索引，在维护分区时，可能造成索引无法使用，需要检查分区索引状态及重建无效索引.

alter index sf2021_idx_hash rebuild partition sys_p66;

前缀分前缀索引

--在本地分区创建唯一索引时，索引必须包括分区列,例如create unique index sf2021_idx_pk on sf2021(sf_id,insert_time) local;--当需要定义主键约束时，首先创建一个唯一索引，再添加约束（分步执行，可以单独管理），如create unique index sf2021_idx_pk on sf2021(sf_id,insert_time) local;alter table sf2021 add constraint sf2021_idx_pk primary key(sf_id,insert_time);

维护分区表及索引

--添加分区alter table sf2021 add partition p202109 values less than (to_date('202108','yyyymm')) tablespace users;--截断分区alter table sf2021 truncate partition p202101;--删除分区alter table sf2021 drop partition p202101;--移动分区alter table sf2021 move partition p202106 tablespace mytest;--拆分分区alter table sf2021 split partition pmax at('2021/09') into (partition p202109 tablespace mytest,partition pmax tablespace mytest);--合并分区alter table sf2021 merge partitions p202101,pmax into partition pmax;--查看分区相关索引情况set lines 200 pages 999select index_name,null partition_name,status from dba_indexes where table_name='SF2021' and partitioned='NO'union allselect index_name,partition_name,status from dba_ind_partitionswhere index_name in (select index_name from dba_indexes where table_name='SF2021') order by 1,2,3;