高级日志

19.1 简介

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

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

19.2 使用须知

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

19.3 语法

19.3.1 管理日志辅助表

19.3.1.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定义数据库基表
  1. 修改表时使用<高级日志子句>添加日志表

语法格式

  ALTER TABLE <高级日志子句>;

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”,用于记录源表的操作但不涉及具体数据。

规则与约束:

  1. 每个源表仅支持设置一个日志辅助表。

  2. 表删除的同时删除其日志辅助表。

  3. 表更名时,日志表同步更名。

  4. 由于其日志表名长度不得超过128,因此表名长度不得超过123。

  5. 辅助表仅登记源表相关增删改及TRUNCATE等涉及数据变化的操作,却不涉及具体数据。

  6. 源表执行ADD/DROP/MODIFY COLUMN的DDL操作时,也必须保证日志辅助表为空。

  7. 如果表设置了高级日志功能,禁止或者不建议以下操作:

    1. 禁止对源表创建聚集索引
    2. 禁止删除源表上本存在的聚集索引
    3. 禁止直接对分区表的子表执行DELETE、UPDATE、INSERT以及TRUNCATE
    4. 禁止在ALTER TABLE时,新建、删除或者修改主键,使主键失效或者生效,或者删除主键列
    5. 禁止对临时表、HUGE表和间隔分区表设置高级日志表,禁止查询插入建表方式设置高级日志表。
    6. 禁止直接删除高级日志表以及创建后缀为”$ALOG”的表
    7. 禁止合并分区
    8. 禁止对表加列、删除列和修改列,禁止添加、分裂、交换和删除分区。交换分区时的普通表也禁止带有高级日志
    9. 表备份还原后无法控制数据跟踪,无法保证同步数据的正确性。因此不建议对该表进行备份还原操作,或操作后需要人工干预处理

19.3.3 日志辅助表结构

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

序号 数据类型 说明
1 ORG_ROWID BIGINT 源表ROWID。当OP_TYPE=0时,ORG_ROWID=0
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。

  SQL> 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.同步脚本根据源表的结构有所不同:

  1. 如果源表有聚集主键

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

  1. 如果源表有主键,但不是聚集主键

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

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

  1. 如果没有主键

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

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

19.5 应用实例

19.5.1 创建不带主键的源表

1.创建源表

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

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

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

2.在源表上创建日志辅助表

  ALTER TABLE T01 WITH ADVANCED LOG;

3.查看日志辅助表结构

  SQL> 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), CLUSTER PRIMARY KEY("ORG_ROWID", "OP_TYPE")) STORAGE(ON "MAIN", CLUSTERBTR) ;

4.在源表中删除1行数据。

  DELETE FROM T01 WHERE A=88;

  SQL> SELECT * FROM T01$ALOG;

  行号	ORG_ROWID			OP_TYPE		COLMAP

---------- -------------------- ----------- ----------

  1		  1					 5			 NULL

5.在源表中更新1行数据。

  UPDATE T01 SET C='HELLO WORLD' WHERE A=99;

  SQL> SELECT * FROM T01$ALOG;

  行号		ORG_ROWID		OP_TYPE		COLMAP

---------- -------------------- ----------- ----------

  1			 1				 5			 NULL

  2			 2				 4			 0x04

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

  SQL> UPDATE T01 SET C='HELLO WORLD!' WHERE A=99;

  行号		ORG_ROWID		OP_TYPE		COLMAP

---------- -------------------- ----------- ----------

  1			 1				 5			 NULL

  2			 2				 4			 0x04

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

  TRUNCATE TABLE T01;

  SQL> SELECT * FROM T01$ALOG;

  行号		ORG_ROWID		OP_TYPE		COLMAP

---------- -------------------- ----------- ----------

  1			 0				 0			 NULL

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

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

  SQL> SELECT * FROM T01$ALOG;

  行号		ORG_ROWID		OP_TYPE		COLMAP

---------- -------------------- ----------- ------------------

  1			 0				 0			 NULL

  2			 1				 2			 NULL

  3			 1				 3			 0x0000000000000064

9.在源表中插入1行数据。

  INSERT INTO T01 VALUES(1001,1002,1003);

  SQL> SELECT * FROM T01$ALOG;

  行号		ORG_ROWID		OP_TYPE		COLMAP

---------- -------------------- ----------- ------------------

  1			 0				 0			 NULL

  2			 1			 	 2			 NULL

  3			 1				 3			 0x0000000000000064

  4			 101			 1			 NULL 

10.同步数据

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

  CREATE HUGE TABLE HUGE_T01 (*C_ROWID BIGINT*, 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, 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;

  /

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

  SQL> SELECT COUNT(*) FROM HUGE_T01;

  行号	  COUNT(*)

---------- --------------------

  1		   101

19.5.2 创建带主键的源表

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

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

2.查看日志辅助表结构

  SQL> 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) ;

3.清空源表

  TRUNCATE TABLE T01;

  SQL> SELECT * FROM T01$ALOG;

  行号	  ORG_ROWID		OP_TYPE		COLMAP		COL_0		COL_1

-------------------- ----------- ---------- ----------- -----------

  1			0			  0			  NULL		 NULL		 NULL

4.在源表中插入一条记录

  INSERT INTO T01 VALUES(1001,1002,1003);

  SQL> SELECT * FROM T01$ALOG;

  行号	  ORG_ROWID			OP_TYPE		COLMAP		COL_0	COL_1

-------------------- ----------- ---------- ----------- -----------

  1			0				 0			 NULL		 NULL	 NULL

  2			1				 1			 NULL		 1001	 1002

5.同步数据

创建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, 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;

  /

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

  SQL> SELECT * FROM HUGE_T01;

  行号	  A			  B			C

---------- ----------- ----------- ----

  1		   1001		   1002		 1003
微信扫码
分享文档
扫一扫
联系客服