时间戳

首页 > 解决方案 > 如何比较Oracle和SQL Server之间的大表（亿行）数据

sql - 如何比较Oracle和SQL Server之间的大表（亿行）数据

问题描述

我有一个进程填充一个有超过 1 亿行的 oracl 表。表结构如下

**ORACLE_TABLE**
id|contractdatetime|Attr3|Attr4|Attr5

的组合(id,contractdatetime)在此表中是唯一的，它使用外部进程填充。

总数distinct id仅约30000。每个 id 都有一个唯一的 contractdatetime。id 不是唯一的，但组合(id,contractdatetime)是

现在另一个进程在 SQL Server 中填充了一个相同的表

**SQLSERVER_TABLE**
id|contractdatetime|Attr3|Attr4|Attr5

我正在考虑检查数据是否两个表相同的最佳方法。我想我是否可以通过 contractid 获得散列版本并以某种方式聚合 Oracle 中的所有其他属性。如果我可以在 SQL Server 中做同样的事情，我将能够在 excel 本身（30000）行中进行比较。

我已经搜索了堆栈溢出，但无法获得 MD5_XOR 的相同功能或任何可以帮助实现此目的的功能，如下面的链接。 http://www.db-nemec.com/MD5/CompareTablesUsingMD5Hash.html

使用链接服务器等的其他选项在获得批准方面会更加困难。

有没有什么好的方法可以解决这个问题

标签： sqlsql-serverdatabaseoracle

解决方案

为了在 Oracle 和 SQL Server 表之间进行快速、高级的比较，您可以使用函数的聚合STANDARD_HASH和HASH_BYTES.

甲骨文代码

--Create a simple table.
create table table1
(
    id number,
    contractdatetime date,
    Attr3 varchar2(100),
    Attr4 varchar2(100),
    Attr5 varchar2(100)
);

--Insert 4 rows, the first three will be identical between databases,
--the last row will be different.
insert into table1 values (1, date '2000-01-01', 'a', 'a', 'a');
insert into table1 values (2, date '2000-01-01', 'b', 'b', 'b');
insert into table1 values (2, date '2000-01-02', null, null, null);
insert into table1 values (3, date '2000-01-02', 'Oracle', 'Oracle', 'Oracle');
commit;

select
    id,
    --Format the number
    trim(to_number(
        --Sum per group.
        sum(
            --Convert to a number.
            to_number(
                --Get the first 14 bytes. This seems to be the maximum that SQL Server can handle
                --before it runs into math errors.
                substr(
                    --Hash the value.
                    standard_hash(
                        --Concatenate the values using (hopefully) unique strings to separate the
                        --columns and represent NULLs (because the hashing functions treat nulls differently.)
                        nvl(to_char(contractdatetime, 'YYYY-MM-DD HH24:MI:SS'), 'null') || 
                        '-1-' || nvl(attr3, 'null') || '-2-' || nvl(attr3, 'null') || '-3-' || nvl(attr3, 'null')
                        , 'MD5')
                    , 1, 14)
                , 'xxxxxxxxxxxxxxxxxxxx'))
        , '99999999999999999999')) hash
from table1
group by id
order by 1;

SQL Server 代码

create table table1
(
    id numeric,
    contractdatetime datetime,
    Attr3 varchar(100),
    Attr4 varchar(100),
    Attr5 varchar(100)
);

insert into table1 values (1, cast('2000-01-01 00:00:00.000' as datetime), 'a', 'a', 'a');
insert into table1 values (2, cast('2000-01-01 00:00:00.000' as datetime), 'b', 'b', 'b');
insert into table1 values (2, cast('2000-01-02 00:00:00.000' as datetime), null, null, null);
insert into table1 values (3, cast('2000-01-02 00:00:00.000' as datetime), 'SQL Server', 'SQL Server', 'SQL Server');
commit;

select
    id,
    sum(
        convert(bigint, convert(varbinary, 
            substring(
                hashbytes('MD5',
                    isnull(convert(varchar(19), contractdatetime, 20), 'null') +
                    '-1-' + isnull(attr3, 'null') + '-2-' + isnull(attr3, 'null') + '-3-' + isnull(attr3, 'null'))
                , 1, 7)
            , 1))) hash
from table1
group by id
order by 1;

结果

正如预期的那样，前两组的哈希值相同，而第三组的哈希值不同。

Oracle:

ID  HASH
1   50696302970576522
2   69171702324546493
3   50787287321473273

SQL Server

ID  HASH
1   50696302970576522
2   69171702324546493
3   7440319042693061

这是一个Oracle fiddle和一个SQL Server fiddle。

问题

  1. 我假设这个解决方案只有在数据库使用相似的字符集时才有效，或者可能只使用前 127 个 ASCII 字符，这些字符通常在不同的字符集中编码相同。
  2. 哈希冲突的可能性很高（也许是不合理的）。MD5 散列不足以防止加密攻击，但它们足以比较数据集。问题是我必须使用子字符串来使数学适用于 SQL Server。这可能是我对 SQL Server 不够了解的错——BIGIINTS 应该支持大约 19 位的精度，但我的数学只能达到 14 位。我可能在某个地方有一个转换错误。如果您遇到太多碰撞或溢出问题，您可能需要使用“14”和“7”数字。（Oracle 为 14，根据显示的十六进制字符计数。SQL Server 为 7，根据每个十六进制字符可以表示的字符数计数，即 0.5。）

推荐阅读