高级日志
简介
行表和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.