DM8高级日志

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

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

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

创建日志辅助表
创建日志辅助表,有两种方式:一是建表时创建;二是修改表时创建。
1. 建表时候使用< 高级日志子句>创建日志辅助表
语法格式

CREATE TABLE < 表名定义> < 表结构定义>;
< 表名定义> ::= [< 模式名>.] < 表名>
< 表结构定义>::=< 表结构定义1> | < 表结构定义2>
< 表结构定义1>::= (< 列定义> {,< 列定义>} [,< 表级约束定义>{,< 表级约束定义>}]) [ON COMMIT  ROWS] [][< 空间限制子句>] [][< 压缩子句>] []< 高级日志子句> [] []
……
< 高级日志子句>::= WITH ADVANCED LOG
省略号(……)

2. 修改表时使用< 高级日志子句>添加日志表
语法格式

ALTER TABLE < 高级日志子句>;

删除日志辅助表
语法格式

ALTER TABLE xxx WITHOUT ADVANCED LOG;

删除日志辅助表的数据
语法格式

ALTER TABLE  TRUNCATE ADVANCED LOG;

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

使用日志辅助表的规则与约束
日志辅助表命名为“表名$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) 表备份还原后无法控制数据跟踪,无法保证同步数据的正确性。因此不建议对该表进行备份还原操作,或操作后需要人工干预处理

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

列                   数据类型           说明
ORG_ROWID            BIGINT             源表ROWID。当OP_TYPE=0时,ORG_ROWID=0
OP_TYPE              SMALLINT           登记记录日志动作。
                                        0:TRUNCATE
                                        1:行插入
                                        2:批量插入起始
                                        3:批量插入结束
                                        4:更新
                                        5:删除
                                        6:删除后再插入(仅用于堆表)

COLMAP               VARBINARY(2048)    当OP_TYPE=3时,记录的是批量插入结束的ROWID;
                                        当OP_TYPE=4时,是记录的更新列的列号。例如0xA3,即二进制的10100011,
                                        表示更新的列为第1、2、6、8列,与DM_BIT_TEST()配合使用;其他情况为null

COL_0     与源表的第一个主键列类型相同  源表的第一个主键列
COL_1     与源表的第二个主键列类型相同  源表的第二个主键列
COL_n     ...                           ...

系统过程
高级日志辅助表中的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);

LINEID     DM_BIT_TEST(0xF1,5) DM_BIT_TEST(1011,3)
---------- ------------------- -------------------
1          1                   0

使用高级日志同步数据的原则
用户根据表定义创建数据同步的目标表,自己编写同步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和主键列辅助源表定位。使用主键列定位目标表。
2) 如果源表有主键,但不是聚集主键
直接根据org_rowid定位数据,最好不要使用主键列来定位源表。主键列仅用来定位目标表。
如果该情况下更新了主键列,对于聚集主键,将是删除后更新,如果不是聚集主键,仍是记录更新,日志辅助表中的主键列仍是原值,所以非聚集主键时主键列不要用来定位源表。
3) 如果没有主键
使用org_rowid来进行源表的定位;目标表的定义根据用户自己的方式使用org_rowid定位。
6. 如果源表中没有聚集索引,批量插入时可以根据OP_TYPE=3时的org_rowid(批量插入起始ROWID)和COLMAP中的数据(批量插入结束ROWID)范围查询源表插入目标表;如果有聚集索引,考虑到组合索引无法进行范围查询,只能使用第一个主键和rowid进行范围查询。
7. MPP环境下,因为高级日志表是本地表,所以同步数据的时候,只能各个节点单独做同步。

应用实例
创建不带主键的源表
1. 创建源表

SQL> Create table t01(a int, b int, c varchar);
executed successfully
used time: 16.049(ms). Execute id is 2438.

SQL> insert into t01 values(88,88, '原始数据1');
affect rows 1

used time: 0.689(ms). Execute id is 2440.
SQL> insert into t01 values(99,99, '原始数据2');
affect rows 1

used time: 0.430(ms). Execute id is 2442.

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

SQL> Alter table t01 with advanced log;
executed successfully
used time: 28.284(ms). Execute id is 2443.

3. 查看日志辅助表结构

SQL> Select tabledef('SYSDBA','T01$ALOG');

LINEID     TABLEDEF('SYSDBA','T01$ALOG')
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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) ;

used time: 0.846(ms). Execute id is 2445.

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

SQL> delete from t01 where a=88;
affect rows 1

used time: 1.329(ms). Execute id is 2447.

SQL> Select * from t01$alog;

LINEID     ORG_ROWID            OP_TYPE     COLMAP
---------- -------------------- ----------- ----------
1          1                    5           NULL

used time: 0.567(ms). Execute id is 2449.

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

SQL> update t01 set c='hello world' where a=99;
affect rows 1

used time: 0.907(ms). Execute id is 2451.
SQL> Select * from t01$alog;

LINEID     ORG_ROWID            OP_TYPE     COLMAP
---------- -------------------- ----------- ----------
1          1                    5           NULL
2          2                    4           0x04

used time: 0.220(ms). Execute id is 2452.

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;
affect rows 1

used time: 0.976(ms). Execute id is 2457.
SQL> Select * from t01$alog;

LINEID     ORG_ROWID            OP_TYPE     COLMAP
---------- -------------------- ----------- ----------
1          1                    5           NULL
2          2                    4           0x04

used time: 0.307(ms). Execute id is 2458.

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

SQL> Truncate table t01;
executed successfully
used time: 28.025(ms). Execute id is 2460.
SQL> Select * from t01$alog;

LINEID     ORG_ROWID            OP_TYPE     COLMAP
---------- -------------------- ----------- ----------
1          0                    0           NULL

used time: 0.414(ms). Execute id is 2461.

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

SQL> insert into t01 select level a,level+1 b,level c connect by level< =100 order by a,b; affect rows 100 used time: 2.692(ms). Execute id is 2464. SQL> Select * from t01$alog;

LINEID     ORG_ROWID            OP_TYPE     COLMAP
---------- -------------------- ----------- ------------------
1          0                    0           NULL
2          1                    2           NULL
3          1                    3           0x0000000000000064

used time: 0.343(ms). Execute id is 2466.

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

SQL> insert into t01 values(1001,1002,1003);
affect rows 1

used time: 0.539(ms). Execute id is 2468.
SQL> Select * from t01$alog;

LINEID     ORG_ROWID            OP_TYPE     COLMAP
---------- -------------------- ----------- ------------------
1          0                    0           NULL
2          1                    2           NULL
3          1                    3           0x0000000000000064
4          101                  1           NULL

used time: 0.178(ms). Execute id is 2470.

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

SQL> create huge table huge_t01 (c_rowid bigint, a int, b int, c varchar(1024));
executed successfully
used time: 26.784(ms). Execute id is 2474.

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

SQL> declare
2   /*遍历日志表的游标*/
3   cursor c IS select * from t01$alog for update;
4   /*同步用的变量*/
5   r t01$alog %rowtype;
6   /*同步批量插入用的变量*/
7   bi_start t01$alog %rowtype;
8   set_sql varchar;
9   upd_sql varchar;
10  i int;
11  begin
12  /*遍历日志表,根据各记录的op_type进行同步*/
13  open c;
14  loop
15  fetch c into r;
16  exit when c%notfound;
17  if (r.op_type = 0) then
18  print 'truncate' ;
19  execute immediate 'truncate table huge_t01;';
20  elseif (r.op_type = 1 or r.op_type = 6) then
21  print 'insert ' || r.org_rowid;
22  execute immediate 'insert into huge_t01 select rowid,* from t01 where rowid=?;' using r.org_rowid;
23  elseif (r.op_type = 2) then
24  bi_start = r;
25  print 'batch insert start';
26  elseif (r.op_type = 3) then
27  print 'batch insert last ' || bi_start.org_rowid || ' ' || cast( r.colmap as bigint);
28  execute immediate 'insert into huge_t01 select rowid,* from t01 where rowid>= ? and rowid< = ?;' using r.org_rowid, cast(r.colmap as bigint); 29 elseif (r.op_type = 4) then 30 print 'update ' || r.org_rowid; 31 set_sql = ''; 32 i = 0; 33 if (dm_bit_test(r.colmap,1)) = 1 then set_sql = set_sql || 'a = org.a'; i = i+1; end if; 34 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;
35  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;
36  upd_sql = 'declare org t01%rowtype; begin select * into org from t01 where rowid=?; update huge_t01 set ' || set_sql || ' where c_rowid=?; end;';
37  execute immediate upd_sql using r.org_rowid, r.org_rowid;
38  elseif (r.op_type = 5) then
39  print 'delete ' || r.org_rowid;
40  execute immediate 'delete from huge_t01 where c_rowid=?;' using r.org_rowid;
41  end if;
42  end loop;
43  close c;
44  /*清理日志表*/
45  execute immediate 'alter table t01 truncate advanced log';
46  end;
47  /
DMSQL executed successfully
used time: 197.177(ms). Execute id is 2478.

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

SQL> Select count(*) from huge_t01;

LINEID     COUNT(*)
---------- --------------------
1          101

used time: 0.622(ms). Execute id is 2479.

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

SQL> Create table t01(a int, b int, c varchar, primary key(a,b)) with advanced log;
executed successfully
used time: 14.030(ms). Execute id is 2834.

2. 查看日志辅助表结构

SQL> Select tabledef('SYSDBA','T01$ALOG');

LINEID     TABLEDEF('SYSDBA','T01$ALOG')
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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) ;

used time: 0.637(ms). Execute id is 2844.

3. 清空源表

SQL> Truncate table t01;
executed successfully
used time: 14.417(ms). Execute id is 2849.

SQL> Select * from t01$alog;

LINEID     ORG_ROWID            OP_TYPE     COLMAP     COL_0       COL_1
---------- -------------------- ----------- ---------- ----------- -----------
1          0                    0           NULL       NULL        NULL

used time: 0.760(ms). Execute id is 2857.

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

SQL> insert into t01 values(1001,1002,1003);
affect rows 1

used time: 0.621(ms). Execute id is 2860.
SQL> Select * from t01$alog;

LINEID     ORG_ROWID            OP_TYPE     COLMAP     COL_0       COL_1
---------- -------------------- ----------- ---------- ----------- -----------
1          0                    0           NULL       NULL        NULL
2          1                    1           NULL       1001        1002

used time: 0.313(ms). Execute id is 2861.

5. 同步数据
创建huge表。

SQL> create huge table huge_t01 (a int, b int, c varchar(1024), primary key(a,b));
executed successfully
used time: 24.819(ms). Execute id is 2868.

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

SQL> declare
2   /*遍历日志表的游标*/
3   cursor c IS select * from t01$alog for update;
4   /*同步用的变量*/
5   r t01$alog %rowtype;
6   /*同步批量插入用的变量*/
7   bi_start t01$alog %rowtype;
8   set_sql varchar;
9   upd_sql varchar;
10  i int;
11  begin
12  /*遍历日志表,根据各记录的op_type进行同步*/
13  open c;
14  loop
15  fetch c into r;
16  exit when c%notfound;
17  if (r.op_type = 0) then
18  print 'truncate' ;
19  execute immediate 'truncate table huge_t01;';
20  elseif (r.op_type = 1 or r.op_type = 6) then
21  print 'insert ' || r.org_rowid;
22  execute immediate 'insert into huge_t01 select * from t01 where rowid=?;' using r.org_rowid;
23  elseif (r.op_type = 2) then
24  bi_start = r;
25  print 'batch insert start';
26  elseif (r.op_type = 3) then
27  print 'batch insert last ' || bi_start.org_rowid || ' ' || cast( r.colmap as bigint);
28  execute immediate 'insert into huge_t01 select * from t01 where rowid>= ? and rowid< = ?;' using r.org_rowid, cast(r.colmap as bigint); 29 elseif (r.op_type = 4) then 30 print 'update ' || r.org_rowid; 31 set_sql = ''; 32 i = 0; 33 if (dm_bit_test(r.colmap,1)) = 1 then set_sql = set_sql || 'a = org.a'; i = i+1; end if; 34 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;
35  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;
36  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;';
37  execute immediate upd_sql using r.org_rowid, r.col_0, r.col_1;
38  elseif (r.op_type = 5) then
39  print 'delete ' || r.org_rowid;
40  execute immediate 'delete from huge_t01 where a= ? and b = ?;' using r.col_0, r.col_1;
41  end if;
42  end loop;
43  close c;
44  /*清理日志表*/
45  execute immediate 'alter table t01 truncate advanced log';
46  end;
47  /
DMSQL executed successfully
used time: 134.846(ms). Execute id is 2872.

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

SQL> select * from huge_t01;

LINEID     A           B           C
---------- ----------- ----------- ----
1          1001        1002        1003

used time: 1.276(ms). Execute id is 2875.

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.