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