1 达梦数据库中的聚集索引和非聚集索引

从物理存储角度进行分类，可分为聚集索引和非聚集索引。
聚集索引（又称为一级索引、主索引）：聚集索引就是按照聚集索引键构造一颗B树，表数据存储在B树叶子节点上，通过定位索引可直接在B树中找到数据。每一个表有且只有一个聚集索引。
非聚集索引（又称为二级索引、辅助索引）：将二级索引列和聚集索引列共同存储在B树叶子节点上。如果查找非聚集索引键值或聚集索引键值可直接在B树中找到；如果查找索引键值以外的数据，则需要回到一级索引中进行查找。每一个表可以有多个非聚集索引。
DM8中表（列存储表和堆表除外）都是使用B+树索引结构管理的，每一个普通表都有且仅有一个聚集索引，数据都通过聚集索引键排序，根据聚集索引键可以快速查询任何记录。
当建表语句未指定聚集索引键时，DM8的默认聚集索引键是ROWID。若指定索引键，表中数据都会根据指定索引键排序。
在dm.ini配置文件中，可以通过指定PK_WITH_CLUSTER使表中的主键自动转化为聚集主键。默认情况下，PK_WITH_CLUSTER为0，即建表时指定的主键不会自动转化为聚集主键；若为1，则主键自动变为聚集主键。PK_WITH_CLUSTER对水平分区表、列存储表和堆表无效。

2 过程/语句块中通过rowid作为过滤条件删除慢

好了，前面介绍了一些达梦物理的存储结构，下面开始正文，介绍下某些场景下，达梦为什么通过rowid删除会慢的？
首先我们来创建数据环境：

2.1、创建表和数据

2.1.1) 创建表

CREATE TABLE “TB”
(
“TA_CODE” VARCHAR2(18) DEFAULT ’ ’ NOT NULL,
“BUSIN_CODE” VARCHAR2(6) DEFAULT ’ ’ NOT NULL,
“SORT_FLAG” VARCHAR2(1) DEFAULT ’ ’ NOT NULL,
“CFM_DATE” INTEGER DEFAULT 0 NOT NULL,
“DETAIL_CFM_NO” VARCHAR2(32) DEFAULT ’ ’ NOT NULL,
“CFM_NO” VARCHAR2(32) DEFAULT ’ ’ NOT NULL,
“ORI_CFM_NO” VARCHAR2(32) DEFAULT ’ ’ NOT NULL,
CONSTRAINT “PK_TB” cluster PRIMARY KEY(“TA_CODE”, “DETAIL_CFM_NO”)) ;

2.1.2) 插入数据

begin
FOR i in 1…10000
loop
insert into TB
select
dbms_random.STRING(‘U’, 18) ,
dbms_random.STRING(‘U’, 6) ,
dbms_random.STRING(‘U’, 1) ,
cast(dbms_random.value(1, 10000) as int),
dbms_random.STRING(‘U’, 3) ,
dbms_random.STRING(‘U’, 3) ,
dbms_random.STRING(‘U’, 3);
end loop;
commit;
end ;
/

2.1.3） 查询数据分布情况

select count(cfm_date) cnt ,cfm_date from tb group by cfm_date order by count(cfm_date) desc;
查看数据的cnt总量不大，修改数据：
update tb set cfm_date=9454 where cfm_date<5000;
commit;
再次查询，确认cfm_date=9454的记录大概有5088行这样。

2.1.4）语句块中执行批量删除

下面写个语句块，进行通过rowid删除cfm_date=9454的记录：
DECLARE
type type_rowid is table of bigint ;
v_rowid type_rowid:=type_rowid();
cursor cur_rowid is select a.rowid from tb a
where 1 = 1 and cfm_date=9454 order by a.rowid;
cnt int;
BEGIN
print ‘111:’||sysdate;
open cur_rowid;
loop
fetch cur_rowid bulk collect into v_rowid ;
if v_rowid.count >0 then
forall i in v_rowid.first…v_rowid.last
delete from tb a where a.rowid = v_rowid(i);
end if;
commit;
exit
when cur_rowid%notfound;
end loop;
close cur_rowid;
v_rowid.delete();
commit;
print ‘222:’||sysdate;
END;
/
执行成功, 影响行数0, 执行耗时6秒 56毫秒. 执行号:1596
111:2022-10-30 14:34:09
222:2022-10-30 14:34:15
影响了5,088条记录
删除5088行达梦中需要6秒，同样的sql在Oracle上是毫秒级，那为什么达梦会这些慢呢？

2.1.5） 查看过程中删除语句的计划

查看计划存储的cache_item值：
select cache_item,sqlstr from v$cachepln where sqlstr like ‘%DECLARE%’;
根据cache_item值，将计划dump到trace文件
ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME PLNDUMP,LEVEL 1102829728’;

在达梦的trace文件目录下(默认是当前库目录的同层trace目录下，可以通过TRACE_PATH设置)，找到该文件进行查看：
SQL_STR:
DELETE FROM TB AS A WHERE EXISTS( SELECT 1 FROM TABLE(V_ROWID) AS FORALL_ARRAY_00000000612E23CC WHERE A.ROWID = V_ROWID(I))
1 #DELETE : [0, 0, 0]; table(TB), type(select), mpp_opt(0)
2 #PIPE2: [1, 500, 104]
3 #PRJT2: [1, 500, 104]; exp_num(3), is_atom(FALSE)
4 #SLCT2: [1, 500, 104];
5 #CSCN2: [1, 10000, 104]; INDEX33555777(TB)
6 #SPL2: [1, 20, 8]; key_num(1), spool_num(0), has_variable(1), sites(-)
7 #PRJT2: [1, 20, 8]; exp_num(1), is_atom(FALSE)
8 #SLCT2: [1, 20, 8];
9 #ASCN: [1, 200, 8]; used_in_sss3(1)
从上面的计划可以看出，达梦中的forall批量处理语句改写成相关子查询的方式，计划是走的全表扫描，而Oracle是直接可以通过rowid定位。那在验证下不使用批量，达梦的计划呢？
explain delete from tb a where a.rowid =1
1 #DELETE : [0, 0, 0]; table(TB), type(select), mpp_opt(0), hp_opt(0)
2 #PRJT2: [1, 122, 104]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [1, 122, 104]; A.ROWID = var2
4 #CSCN2: [1, 4912, 104]; INDEX33555777(TB as A)
计划上看，也没有用到rowid上的索引，这是为什么呢？

2.1.6）优化调整

通过前面的介绍，DM8的默认聚集索引键是ROWID。若指定索引键，表中数据都会根据指定索引键排序。因为建表指定了聚集索引建：CONSTRAINT “PK_TB” cluster PRIMARY KEY(“TA_CODE”, “DETAIL_CFM_NO”)因此rowid上是没有索引的，根据计划走全表扫描。基于上面的分析，将表结构调整为非聚集主键CONSTRAINT “PK_TB” not cluster PRIMARY KEY(“TA_CODE”, “DETAIL_CFM_NO”),再次验证：
delete from tb a where a.rowid =1；
1 #DELETE : [0, 0, 0]; table(TB), type(select), mpp_opt(0), hp_opt(0)
2 #PRJT2: [1, 1, 8]; exp_num(1), is_atom(FALSE)
3 #CSEK2: [1, 1, 8]; scan_type(ASC), INDEX33555778(TB as A), scan_range[exp_cast(1),exp_cast(1)]
计划直接根据rowid索引定位扫描。因此在将存储过程过的forall语句改写为for循环的方式验证：
DECLARE
type type_rowid is table of bigint ;
v_rowid type_rowid:=type_rowid();
cursor cur_rowid is select a.rowid from tb a
where 1 = 1 and cfm_date=9454 order by a.rowid;
cnt int;
BEGIN
print ‘111:’||sysdate;
open cur_rowid;
loop
fetch cur_rowid bulk collect into v_rowid ;
if v_rowid.count >0 then
–forall i in v_rowid.first…v_rowid.last
for i in v_rowid.first…v_rowid.last LOOP
delete from tb a where a.rowid = v_rowid(i);
end loop;
end if;
commit;
exit
when cur_rowid%notfound;
end loop;
close cur_rowid;
v_rowid.delete();
commit;
print ‘222:’||sysdate;
END;
执行成功, 影响行数0, 执行耗时75毫秒. 执行号:1684
111:2022-10-30 14:53:36
222:2022-10-30 14:53:36
相比优化前提升了百倍。

1.2 总结

主要原因：因为建表语句中主键字段，定义了聚集主键（cluster primary key），表会按照聚集主键列组织数据，rowid上就没有索引了。
解决办法：1）将主键字段定义为非聚集主键（not cluster primary key），表默认会按照rowid来组织数据（相当于rowid上创建了聚集唯一索引），那么，针对rowid的查询或删除就可以通过索引精确定位了，效率自然就快了。
2）删除的匿名块中–forall i in v_rowid.first…v_rowid.last 修改为 for i in v_rowid.first…v_rowid.last loop。主要是forall批量删除内部会转化为子查询，无法用到索引。因此需要将forall 修改为for。
3）另外解释一下，达梦默认建表是索引组织表，rowid是逻辑的。Oracle默认建表是堆表，rowid是物理的，可直接定位数据。因此该删除模块，也可以考虑改造成，通过主键列进行过滤删除。