环境说明：麒麟V10
数据库：DM8
创建测试表
CREATE TABLE “SYSDBA”.“M_CUST_INDICATOR_FACT”
(
“ETL_DATE” DATE,
“SRCSYS_NO” VARCHAR(10),
“AGT_NO” VARCHAR(30),
“ACCT_NO” VARCHAR(30),
“ACCT_NAME” VARCHAR(100),
“ORG_NO” VARCHAR(30))
PARTITION BY LIST(“ETL_DATE”)
(
PARTITION “P20220101” VALUES(DATE’2022-01-01’) ,
PARTITION “P20220102” VALUES(DATE’2022-01-02’) ,
PARTITION “P20220103” VALUES(DATE’2022-01-03’) ,
PARTITION “P20220104” VALUES(DATE’2022-01-04’) ,
PARTITION “P20220105” VALUES(DATE’2022-01-05’) ,
PARTITION “P20220106” VALUES(DATE’2022-01-06’) ,
PARTITION “P20220107” VALUES(DATE’2022-01-07’) ,
PARTITION “P20220108” VALUES(DATE’2022-01-08’) ,
PARTITION “P20220109” VALUES(DATE’2022-01-09’) ,
PARTITION “P20220110” VALUES(DATE’2022-01-10’) ,
PARTITION “P20220111” VALUES(DATE’2022-01-11’) ,
PARTITION “P20220112” VALUES(DATE’2022-01-12’) ,
PARTITION “P20220113” VALUES(DATE’2022-01-13’) ,
PARTITION “P20220114” VALUES(DATE’2022-01-14’) ,
PARTITION “P20220115” VALUES(DATE’2022-01-15’)
);

插入数据
declare
vdate date;
begin
vdate:=to_date(‘20220101’,‘yyyymmdd’);
for i in 1…15 loop

insert into M_CUST_INDICATOR_FACT
select
vdate ETL_DATE,
‘CORE’ SRCSYS_NO,
dbms_random.string (‘a’, 15) AGT_NO,
dbms_random.string (‘x’, 20) acct_no,
dbms_random.string (‘x’, 10) acct_name,
‘0101’ org_no
from dual connect by level <= 300000;
commit;
vdate:=vdate+1;
end loop;
end;
一 创建主键
在分区表上添加主键与普通表一样,添加主键之后,发现自动建了索引,该索引由系统维护。
若主键列不包含分区键时,索引为全局索引
alter table M_CUST_INDICATOR_FACT add PRIMARY key(acct_no);

若是复合主键,包含分区键时,自动创建的索引为局部索引
删除原主键alter table M_CUST_INDICATOR_FACT drop PRIMARY key;
alter table M_CUST_INDICATOR_FACT add PRIMARY key(etl_date,acct_no);

二 唯一索引
在分区表上创建唯一索引时,默认是局部索引. 如AGT_NO列, 此时报错提示” 聚集主键必须包含全部分区列”
create unique index idx_M_CUST_INDICATOR_FACT on M_CUST_INDICATOR_FACT(agt_no);

带上分区键后,创建成功,可以看到创建的是局部索引
create unique index idx_M_CUST_INDICATOR_FACT on M_CUST_INDICATOR_FACT(etl_date,agt_no);

若创建全局唯一索引,需带上GLOBAL,此时不带上分区键也能成功
create unique index idx_M_CUST_INDICATOR_FACT on M_CUST_INDICATOR_FACT(agt_no) global;

三 普通索引
创建索引时,默认为局部索引
create index idx_M_CUST_INDICATOR_FACT_NAME on M_CUST_INDICATOR_FACT(acct_name);

带上global为全局索引
create index idx_M_CUST_INDICATOR_FACT_NAME on M_CUST_INDICATOR_FACT(acct_name) global;

四 全局索引与局部索引的性能对比
1查询SQL:
select * from M_CUST_INDICATOR_FACT
where acct_name=‘J08O7HGFEG’
acct_name列上索引情况 查询耗时
无索引 17秒
全局索引 2毫秒
局部索引 3毫秒
有索引时查询,无论是全局索引还是局部索引,性能都很好,差别不大

2 分区DDL操作
清空分区
alter table M_CUST_INDICATOR_FACT truncate partition P20220105;
分区表含索引情况 操作耗时 操作后索引状态
全局索引 25秒 有效
局部索引 70毫秒 有效
分区表上存在全局索引时,对子分区的DDL操作,耗时很长,需重建全局索引,此过程耗时大,锁表
分区表上只有局部索引时, 对子分区的DDL操作,速度非常快,不会有索引重建的耗时,几乎感觉不到影响
五 结论
局部索引只索引和维护单个分区上的数据,全局索引维护的全表数据,因此开销比局部索引大,因此分区表优先考虑局部索引
1 根据实际需要,创建一个不含分区键的主键,此索引为全局索引
2 创建唯一索引时,带上分区键,建成局部索引
3 普通列优先局部索引,数据维护简单,DDL操作快