数据校验时一项比较重要的工作,例如,在备份恢复时,需要检查恢复的数据与源数据的差异,在数据库迁移后同样需要进行数据的校验。
一 搭建环境
1.1 分别新建表old和表new
create table old(
oldid number(8),
oname varchar2(20),
sex varchar2(2),
age number(3),
phone number(11));
这里建的表new跟表old结构类似:
create table new(
newid number(8),
nname varchar2(20),
sex varchar2(2),
age number(3),
phone number(11));
1.2 分别插入测试数据
随机向表old插入10条数据:
Insert into old values (1001,'张杰','男',30,13878967832);
Insert into old values (1002,'李宇春','女',28,13889534562);
Insert into old values (1003,'周杰伦','男',36,15978953421);
Insert into old values (1004,'王菲','女',42,15087899012);
Insert into old values (1005,'刘德华','男',48,17078965456);
Insert into old values (1006,'宋小宝','男',30,13435678291);
Insert into old values (1007,'马云','女',38,13432345123);
Insert into old values (1008,'刘翔','男',32,13098734561);
Insert into old values (1009,'普京','男',45,13435678291);
Insert into old values (10010,'张宇','男',34,13023109287);
随机向表new插入15条数据,其中有部分数据与old相同:
Insert into new values (1001,'张杰','男',30,13878967832);
Insert into new values (1002,'李宇春','女',28,13889534562);
Insert into new values (1003,'刘强东','男',36,15978953421);
Insert into new values (1004,'王菲','女',42,15087899012);
Insert into new values (1005,'刘德华','男',48,17078965456);
Insert into new values (1006,'赵本山','男',30,13435678291);
Insert into new values (1007,'马云','女',38,13432345123);
Insert into new values (1008,'刘翔','男',32,13098734561);
Insert into new values (1009,'奥巴马','男',45,13435678291);
Insert into new values (10010,'张宇','男',34,13023109287);
Insert into new values (10011,'付雨','女',32,13089759287);
Insert into new values (10012,'刘偶','男',37,13023100987);
Insert into new values (10013,'张微','女',30,13021232387);
二 数据校验
2.1 MINUS
SELECT *
FROM (SELECT * FROM NEW MINUS SELECT * FROM OLD)
UNION
SELECT *
FROM (SELECT * FROM OLD MINUS SELECT * FROM NEW);
2.2 NOT EXISTS
SELECT *
FROM (SELECT *
FROM NEW A
WHERE NOT EXISTS (SELECT 1
FROM OLD B
WHERE A.NEWID = B.OLDID
AND A.NNAME = B.ONAME))
UNION
SELECT *
FROM (SELECT *
FROM OLD A
WHERE NOT EXISTS (SELECT 1
FROM NEW B
WHERE A.OLDID = B.NEWID
AND A.ONAME = B.NNAME));
三 方法对比
3.1 MINUS
MINUS方法操作简便,常用于在小表之间进行对比。由于 SORT 排序的开销比较大,所以在大表之间使用 MINUS的话,效率会比较低,且不支持 LOB 和LONG 这样的大对象。
3.2 NOT EXISTS
NOT EXISTS方法不仅操作简便,且在同一数据库校验两个数据量较大的表时的效率要比 MINUS 方式高,但如果跨库查询的话,则效率会因 FILTER 操作而急剧下降。not exists同样不支持 CLOB 等大对象。
另外,Toad、PL/SQL Developer 等图形化工具也提供了数据校验功能,其底层原理跟上述方法大同小异,只是通过图形界面省去了写 SQL 语句的麻烦。这种方法同样不支持 LOB、LONG 等对象。