高级日志 | 达梦技术文档

19.1 简介

行表和 HUGE 表在增删改查性能上存在差异，因此在实际的生产环境中，用户可能会同时使用一个行表来管理数据和一个 HUGE 表来分析数据。具体做法是对行表进行增删改操作，然后把行表中的数据复制到 HUGE 表中用于查询或分析。如果每次分析数据时都对行表进行全表查询插入 HUGE 表，性能较低。

为此提出一种解决方案：给行表添加日志辅助表用于记录行表的增删改和 TRUNCATE 操作，可以根据日志表实现对 HUGE 表的增量更新，以此来提高从行表复制数据到 HUGE 表的性能。

19.2 使用须知

增量更新过程，我们只提供日志的记录以及日志记录规则的制定，真正执行增量更新是由用户根据日志记录自行操作。辅助表中登记信息，为某一时间点后源表数据的增量变化信息登记。

19.3 语法

19.3.1 管理日志辅助表

19.3.1.1 创建日志辅助表

创建日志辅助表，有两种方式：一是建表时创建；二是修改表时创建。

建表时候使用 < 高级日志子句 > 创建日志辅助表

语法格式

  CREATE TABLE <表名定义> <表结构定义>;
  <表名定义> ::= [<模式名>.] <表名>
  <表结构定义>::=<表结构定义1> | <表结构定义2>
  <表结构定义1>::= (<列定义> {,<列定义>} [,<表级约束定义>{,<表级约束定义>}]) [ON COMMIT <DELETE | PRESERVE> ROWS] [<PARTITION子句>][<空间限制子句>] [<表空间子句>] [<STORAGE子句>][<压缩子句>] [<ROW MOVEMENT子句>][<高级日志子句>] [<add_log子句>] [<DISTRIBUTE子句>]
  ……
  <高级日志子句>::= WITH ADVANCED LOG
  省略号（……）部分请参考《DM8 Sql语言使用手册》3.5.1.1定义数据库基表

修改表时使用 < 高级日志子句 > 添加日志表

语法格式

  ALTER TABLE <TABLE_NAME> <高级日志子句>;

19.3.1.2 删除日志辅助表

语法格式

  ALTER TABLE <TABLE_NAME> WITHOUT ADVANCED LOG;

19.3.1.3 删除日志辅助表的数据

语法格式

  ALTER TABLE <TABLE_NAME> TRUNCATE ADVANCED LOG;

数据清除后可能导致源表和 HUGE 无法同步，需慎重操作。

19.3.2 使用日志辅助表的规则与约束

日志辅助表命名为“表名 $ALOG”，用于记录源表的操作但不涉及具体数据。

规则与约束：

每个源表仅支持设置一个日志辅助表。
表删除的同时删除其日志辅助表。
表更名时，日志表同步更名。
由于其日志表名长度不得超过 128，因此表名长度不得超过 123。
辅助表仅登记源表相关增删改及 TRUNCATE 等涉及数据变化的操作，却不涉及具体数据。
源表执行 ADD/DROP/MODIFY COLUMN 的 DDL 操作时，也必须保证日志辅助表为空。
如果表设置了高级日志功能，禁止或者不建议以下操作：
1. 禁止对源表创建聚集索引
2. 禁止删除源表上本存在的聚集索引
3. 禁止直接对分区表的子表执行 DELETE、UPDATE、INSERT 以及 TRUNCATE
4. 禁止在 ALTER TABLE 时，新建、删除或者修改主键，使主键失效或者生效，或者删除主键列
5. 禁止对临时表、HUGE 表和间隔分区表设置高级日志表，禁止查询插入建表方式设置高级日志表。
6. 禁止直接删除高级日志表以及创建后缀为”$ALOG”的表
7. 禁止合并分区
8. 禁止对表加列、删除列和修改列，禁止添加、分裂、交换和删除分区。交换分区时的普通表也禁止带有高级日志
9. 表备份还原后无法控制数据跟踪，无法保证同步数据的正确性。因此不建议对该表进行备份还原操作，或操作后需要人工干预处理

19.3.3 日志辅助表结构

高级日志辅助表“表名 $ALOG”的结构如下：

序号	列	数据类型	说明
1	ORG_ROWID	ROWID	源表 ROWID。当 OP_TYPE=0 时，ORG_ROWID=' AAAAAAAAAAAAAAAAAA'
2	OP_TYPE	SMALLINT	登记记录日志动作。 0:TRUNCATE 1:行插入 2:批量插入起始 3:批量插入结束 4:更新 5:删除 6:删除后再插入(仅用于堆表)
3	COLMAP	VARBINARY(2048)	当 OP_TYPE=3 时，记录的是批量插入结束的 ROWID；当 OP_TYPE=4 时，是记录的更新列的列号。例如 0xA3，即二进制的 10100011，表示更新的列为第 1、2、6、8 列，与 DM_BIT_TEST()配合使用；其他情况为 null
4	COL_0	与源表的第一个主键列类型相同	源表的第一个主键列
5	COL_1	与源表的第二个主键列类型相同	源表的第二个主键列
6	Col_n	...	...

19.3.4 系统过程

高级日志辅助表中的 COLMAP 列记录的数据，用&操作只能获取前 64 列的更新情况，因为会数据溢出。

增加系统过程 DM_BIT_TEST（）用于获取一个 VARBINARY 数据的第 N 位的数值。

语法格式

  DM_BIT_TEST(DATA varbinary, nth int);

功能：返回二进制数据 varbinary 第 nth 位是 0 还是 1(最低位序号为 1)。如果超过了位数则返回 0。

例 0xF1 转为二进制后为 11110001，从低位开始第 5 位为 1。二进制 1011 从低位开始第三位为 0。

SELECT DM_BIT_TEST(0xF1,5),DM_BIT_TEST(1011,3);

查询结果如下：

行号	DM_BIT_TEST(0xF1,5)	DM_BIT_TEST(1011,3)
---------- ------------------- ------------------
 	1			1					0

19.4 使用高级日志同步数据的原则

用户根据表定义创建数据同步的目标表，自己编写同步 DMSQL 脚本来进行同步。对于同步，建议遵守如下的原则：

1.如果源表有主键，如果用户没有特殊的限制或要求，目标表最好也设置同样的主键。

2.如果源表没有主键，为了准确同步，最好在目标表上添加一个辅助同步的主键列，同步时将 org_rowid 列的值插入该列中。

3.用户同步数据的脚本基本逻辑如下：

  declare
	//遍历日志表的游标
	cursor c IS select * from t01$alog for update;
	//同步用的变量
	r t01$alog %rowtype;
	//同步批量插入用的变量
	bi_start t01$alog %rowtype;
	org_rec t01%rowtype;
  begin
	//遍历日志表，根据各记录的op_type进行同步
	open c;
	loop
	  fetch c into r;
	  exit when c%notfound;
		if (r.op_type = 0) then
		  print 'truncate' ;
		  execute immediate 'truncate table t01';
		elseif (r.op_type = 1 or r.op_type = 6) then
		  print 'insert ' || r.org_rowid;
		  execute immediate 'insert ....'
		elseif (r.op_type = 2) then
		  bi_start = r;
		  print 'batch insert start';
		elseif (r.op_type = 3) then
		  print 'batch insert last ' || bi_start.org_rowid || ' ' || cast( r.colmap as bigint);
		  execute immediate 'insert ....'
		elseif (r.op_type = 4) then
		  print 'update ' || r.org_rowid;
	  select * into org_rec from t01 where ……;
		  execute immediate 'update ....' using bi_start… r…;
		elseif (r.op_type = 5) then
		  print 'delete ' || r.org_rowid;
		  execute immediate 'delete ....'
		end if;
	end loop;
	close c;
	//清理日志表
	execute immediate 'alter table t01 truncate advanced log';
  end;
  /

4.如果在数据同步时源表仍有并发的 DML，脚本中查询日志时要使用 for update 子句。

5.同步脚本根据源表的结构有所不同：

如果源表有聚集主键

在同步时可使用日志辅助表中的 org_rowid 和主键列辅助源表定位。使用主键列定位目标表。

如果源表有主键，但不是聚集主键

直接根据 org_rowid 定位数据，最好不要使用主键列来定位源表。主键列仅用来定位目标表。

如果该情况下更新了主键列，对于聚集主键，将是删除后更新，如果不是聚集主键，仍是记录更新，日志辅助表中的主键列仍是原值，所以非聚集主键时主键列不要用来定位源表。

如果没有主键

6.使用 org_rowid 来进行源表的定位；目标表的定义根据用户自己的方式使用 org_rowid 定位。如果源表中没有聚集索引，批量插入时可以根据 OP_TYPE=3 时的 org_rowid(批量插入起始 ROWID)和 COLMAP 中的数据(批量插入结束 ROWID)范围查询源表插入目标表；如果有聚集索引，考虑到组合索引无法进行范围查询，只能使用第一个主键和 rowid 进行范围查询。

7.MPP 环境下，因为高级日志表是本地表，所以同步数据的时候，只能各个节点单独做同步。

19.5 应用实例

19.5.1 创建不带主键的源表

创建源表

  CREATE TABLE T01(A INT, B INT, C VARCHAR);

  INSERT INTO T01 VALUES(88,88, '原始数据1');

  INSERT INTO T01 VALUES(99,99, '原始数据2');

在源表上创建日志辅助表

  ALTER TABLE T01 WITH ADVANCED LOG;

查看日志辅助表结构

CALL SP_TABLEDEF('SYSDBA','T01$ALOG');

调用结果如下：

COLUMN_VALUE
-----------------------------------------------------------------------
CREATE TABLE "SYSDBA"."T01$ALOG"
(
"ORG_ROWID" ROWID NOT NULL,
"OP_TYPE" SMALLINT NOT NULL,
"COLMAP" VARBINARY(2048),
CLUSTER PRIMARY KEY("ORG_ROWID", "OP_TYPE")) STORAGE(ON "MAIN", CLUSTERBTR);

在源表中删除 1 行数据。

DELETE FROM T01 WHERE A=88;
SELECT * FROM T01$ALOG;

查询结果如下：

ORG_ROWID          OP_TYPE     COLMAP
------------------ ----------- ----------
AAAAAAAAAAAAAAAAAB 5           NULL

在源表中更新 1 行数据。

UPDATE T01 SET C='HELLO WORLD' WHERE A=99;
SELECT * FROM T01$ALOG;

查询结果如下：

ORG_ROWID     OP_TYPE   COLMAP
------------------ ----------- ----------
AAAAAAAAAAAAAAAAAB 5      NULL
AAAAAAAAAAAAAAAAAC 4      0x04

在源表中再次更新同 1 行数据。这一操作在日志表中没有记录。因为将源表上一条（99,99, '原始数据 2'）的数据更新为（99,99,'hello world'）之后，又再次更新为（99,99,'hello world!'）。这两步更新操作的最终结果就和直接更新为（99,99,'hello world!'）一样，所以两步操作只有一条记录。

UPDATE T01 SET C='HELLO WORLD!' WHERE A=99;
SELECT * FROM T01$ALOG;

查询结果如下：

ORG_ROWID     OP_TYPE   COLMAP
------------------ ----------- ----------
AAAAAAAAAAAAAAAAAB 5      NULL
AAAAAAAAAAAAAAAAAC 4      0x04

先清空源表数据，再查看日志辅助表的变化。发现日志辅助表中也清空了之前的记录，只记录下了清空源表的操作。

TRUNCATE TABLE T01;
SELECT * FROM T01$ALOG;

查询结果如下：

ORG_ROWID     OP_TYPE   COLMAP
------------------ ----------- ----------
AAAAAAAAAAAAAAAAAA 0      NULL

在源表中批量插入 100 行数据。单机情况下，大于 100 条才叫批量插入。

INSERT INTO T01 SELECT LEVEL A,LEVEL+1 B,LEVEL C CONNECT BY LEVEL<=100 ORDER BY A,B;
SELECT * FROM T01$ALOG;

查询结果如下：

ORG_ROWID     OP_TYPE   COLMAP
------------------ ----------- --------------------------
AAAAAAAAAAAAAAAAAA 0      NULL
AAAAAAAAAAAAAAAAAB 2      NULL
AAAAAAAAAAAAAAAAAB 3      0x000000000000000000000064

在源表中插入 1 行数据。

INSERT INTO T01 VALUES(1001,1002,1003);
SELECT * FROM T01$ALOG;

查询结果如下：

ORG_ROWID     OP_TYPE   COLMAP
------------------ ----------- --------------------------
AAAAAAAAAAAAAAAAAA 0      NULL
AAAAAAAAAAAAAAAAAB 2      NULL
AAAAAAAAAAAAAAAAAB 3      0x000000000000000000000064
AAAAAAAAAAAAAAAABl 1      NULL

同步数据

创建 HUGE 表。因为不带主键，为了准确同步，在目标表 HUGE_t01 上添加一个辅助同步的主键列 c_rowid，同步时将 org_rowid 列的值插入该列中

CREATE HUGE TABLE HUGE_T01 (C_ROWID ROWID, A INT, B INT, C VARCHAR(1024));

运行同步脚本。同步脚本由用户根据实际情况自行编写。本例中脚本如下：

  declare
	//遍历日志表的游标
	cursor c IS select * from t01$alog for update;
	//同步用的变量
	r t01$alog %rowtype;
	//同步批量插入用的变量
	bi_start t01$alog %rowtype;
	set_sql varchar;
	upd_sql varchar;
	i int;
  begin
	//遍历日志表，根据各记录的op_type进行同步
	open c;
	loop
	  fetch c into r;
	  exit when c%notfound;
		if (r.op_type = 0) then
		  print 'truncate' ;
		  execute immediate 'truncate table huge_t01;';
		elseif (r.op_type = 1 or r.op_type = 6) then
		  print 'insert ' || r.org_rowid;
		  execute immediate 'insert into huge_t01 select rowid,* from t01 where rowid=?;' using r.org_rowid;
		elseif (r.op_type = 2) then
		  bi_start = r;
		  print 'batch insert start';
		elseif (r.op_type = 3) then
		  print 'batch insert last ' || bi_start.org_rowid || ' ' || cast( r.colmap as bigint);
		  execute immediate 'insert into huge_t01 select rowid,* from t01 where rowid>= ? and rowid<= ?;' using r.org_rowid, sf_build_rowid(1,1,cast(r.colmap as bigint));
		elseif (r.op_type = 4) then
		  print 'update ' || r.org_rowid;
		  set_sql = '';
		  i = 0;
		  if (dm_bit_test(r.colmap,1)) = 1 then set_sql = set_sql || 'a = org.a'; i = i+1; end if;
		  if (dm_bit_test(r.colmap,2)) = 1 then if i > 0 then set_sql = set_sql ||','; end if; set_sql = set_sql || 'b = org.b'; i = i+1; end if;
		  if (dm_bit_test(r.colmap,3)) = 1 then if i > 0 then set_sql = set_sql ||','; end if; set_sql = set_sql || 'c = org.c'; i = i+1; end if;
		  upd_sql = 'declare org t01%rowtype; begin select * into org from t01 where rowid=?; update huge_t01 set ' || set_sql || ' where c_rowid=?; end;';
		  execute immediate upd_sql using r.org_rowid, r.org_rowid;
		elseif (r.op_type = 5) then
		  print 'delete ' || r.org_rowid;
		  execute immediate 'delete from huge_t01 where c_rowid=?;' using r.org_rowid;
		end if;
	end loop;
	close c;
	//清理日志表
	execute immediate 'alter table t01 truncate advanced log';
  end;
  /

查询 HUGE 表中的数据。可以看出，HUGE_t01 上的数据都是源表创建了日志辅助表之后的增量数据。

SELECT COUNT(*) FROM HUGE_T01;

查询结果如下：

COUNT(*)
----------
101

19.5.2 创建带主键的源表

创建带有日志辅助表的源表

CREATE TABLE T01(A INT, B INT, C VARCHAR, PRIMARY KEY(A,B)) WITH ADVANCED LOG;

查看日志辅助表结构

CALL SP_TABLEDEF('SYSDBA','T01$ALOG');

调用结果如下：

 行号 	  COLUMN_VALUE
-------------------------------------------------------------------------
  1		   CREATE TABLE "SYSDBA"."T01$ALOG" ( "ORG_ROWID" BIGINT NOT NULL, "OP_TYPE" SMALLINT NOT NULL, "COLMAP" VARBINARY(2048), "COL_0" INTEGER, "COL_1" INTEGER, CLUSTER PRIMARY KEY("ORG_ROWID", "OP_TYPE")) STORAGE(ON "MAIN", CLUSTERBTR) ;

清空源表

TRUNCATE TABLE T01;
SELECT * FROM T01$ALOG;

查询结果如下：

ORG_ROWID          OP_TYPE     COLMAP     COL_0       COL_1
------------------ ----------- ---------- ----------- -----------
AAAAAAAAAAAAAAAAAA 0           NULL       NULL        NULL

在源表中插入一条记录

INSERT INTO T01 VALUES(1001,1002,1003);
SELECT * FROM T01$ALOG;

查询结果如下：

ORG_ROWID          OP_TYPE     COLMAP     COL_0       COL_1
------------------ ----------- ---------- ----------- -----------
AAAAAAAAAAAAAAAAAA 0           NULL       NULL        NULL
AAAAAAAAAAAAAAAAAB 1           NULL       1001        1002

同步数据

创建 huge 表。

CREATE HUGE TABLE HUGE_T01 (A INT, B INT, C VARCHAR(1024), PRIMARY KEY(A,B));

运行同步脚本。同步脚本由用户根据实际情况自行编写。本例中脚本如下：

declare
	//遍历日志表的游标
	cursor c IS select * from t01$alog for update;
	//同步用的变量
	r t01$alog %rowtype;
	//同步批量插入用的变量
	bi_start t01$alog %rowtype;
	set_sql varchar;
	upd_sql varchar;
	i int;
begin
	//遍历日志表，根据各记录的op_type进行同步
	open c;
	loop
	  fetch c into r;
	  exit when c%notfound;
		if (r.op_type = 0) then
		  print 'truncate' ;
		  execute immediate 'truncate table huge_t01;';
		elseif (r.op_type = 1 or r.op_type = 6) then
		  print 'insert ' || r.org_rowid;
		  execute immediate 'insert into huge_t01 select * from t01 where rowid=?;' using r.org_rowid;
		elseif (r.op_type = 2) then
		  bi_start = r;
		  print 'batch insert start';
		elseif (r.op_type = 3) then
		  print 'batch insert last ' || bi_start.org_rowid || ' ' || cast( r.colmap as bigint);
		  execute immediate 'insert into huge_t01 select * from t01 where rowid>= ? and rowid<= ?;' using r.org_rowid, sf_build_rowid(1,1,cast(r.colmap as bigint));
		elseif (r.op_type = 4) then
		  print 'update ' || r.org_rowid;
		  set_sql = '';
		  i = 0;
		  if (dm_bit_test(r.colmap,1)) = 1 then set_sql = set_sql || 'a = org.a'; i = i+1; end if;
		  if (dm_bit_test(r.colmap,2)) = 1 then if i > 0 then set_sql = set_sql ||','; end if; set_sql = set_sql || 'b = org.b'; i = i+1; end if;
		  if (dm_bit_test(r.colmap,3)) = 1 then if i > 0 then set_sql = set_sql ||','; end if; set_sql = set_sql || 'c = org.c'; i = i+1; end if;
		  upd_sql = 'declare org t01%rowtype; begin select * into org from t01 where rowid=?; update huge_t01 set ' || set_sql || ' where a = ? and b = ?; end;';
		  execute immediate upd_sql using r.org_rowid, r.col_0, r.col_1;
		elseif (r.op_type = 5) then
		  print 'delete ' || r.org_rowid;
		  execute immediate 'delete from huge_t01 where a= ? and b = ?;' using r.col_0, r.col_1;
		end if;
	end loop;
	close c;
	//清理日志表
	execute immediate 'alter table t01 truncate advanced log';
  end;
  /

查询 huge 表中的数据。可以看出，huge_t01 上的数据都是源表创建了日志辅助表之后的增量数据。

SELECT * FROM HUGE_T01;

查询结果如下：

A           B           C
----------- ----------- ----
1001        1002        1003