> 本文主要介绍Oracle 分区索引相关技术 --by Firsouler 2021.8.11 <!-- @import "[TOC]" {cmd="toc" depthFrom=1 depthTo=6 orderedList=false} --> <!-- code_chunk_output --> - [环境准备](#环境准备) - [本地分区索引](#本地分区索引) - [全局分区索引](#全局分区索引) - [前缀分前缀索引](#前缀分前缀索引) - [维护分区表及索引](#维护分区表及索引) - [表级分区操作对分区索引的影响](#表级分区操作对分区索引的影响) <!-- /code_chunk_output --> ### 环境准备 创建分区表 ``` sql --创建表结构 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) as begin for i in 1..tablecount loop insert into mytest.sf2021 select 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 a20 select to_char(insert_time,'yyyy/mm') insert_time,count(*) from sf2021 group by to_char(insert_time,'yyyy/mm'); ``` ### 本地分区索引 > 本地分区索引，只适用于分区表分区键 ``` sql --分区键，创建本地分区索引 create index sf2021_idx_id on sf2021(insert_time) local; --查看分区状态 --索引及分区类型可参考 dba_part_indexes SQL> 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 USABLE SF2021_IDX_ID P202103 USABLE SF2021_IDX_ID P202104 USABLE SF2021_IDX_ID P202105 USABLE SF2021_IDX_ID P202106 USABLE SF2021_IDX_ID P202107 USABLE SF2021_IDX_ID P202108 USABLE SF2021_IDX_ID PMAX USABLE 8 rows selected. --创建全局hash分区 create index sf2021_idx_hash on sf2021(sf_id) global partition by hash(sf_id) partitions 6; ``` ### 全局分区索引 全局分区索引 可分为范围和散列. ``` sql --创建全局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 USABLE SF2021_IDX_HASH SYS_P62 USABLE SF2021_IDX_HASH SYS_P63 USABLE SF2021_IDX_HASH SYS_P64 USABLE SF2021_IDX_HASH SYS_P65 USABLE SF2021_IDX_HASH SYS_P66 USABLE 6 rows selected. ``` 注意，全局分区索引，在维护分区时，可能造成索引无法使用，需要检查分区索引状态及重建无效索引. ``` sql alter index sf2021_idx_hash rebuild partition sys_p66; ``` ### 前缀分前缀索引 ``` sql --在本地分区创建唯一索引时，索引必须包括分区列,例如 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); ``` ### 维护分区表及索引 ``` sql --添加分区 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 999 select index_name,null partition_name,status from dba_indexes where table_name='SF2021' and partitioned='NO' union all select index_name,partition_name,status from dba_ind_partitions where index_name in (select index_name from dba_indexes where table_name='SF2021') order by 1,2,3; ``` ### 表级分区操作对分区索引的影响