本文主要介绍Oracle 分区索引相关技术 —by Firsouler 2021.8.11
环境准备
创建分区表
--创建表结构
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');
本地分区索引
本地分区索引，只适用于分区表分区键
--分区键，创建本地分区索引
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;
全局分区索引
全局分区索引 可分为范围和散列.
--创建全局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.
注意，全局分区索引，在维护分区时，可能造成索引无法使用，需要检查分区索引状态及重建无效索引.
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 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;
表级分区操作对分区索引的影响
