How to calculate checksum in Oracle?

Question

I do a merge on table and need to update data if they are different. In MSSQL I usually do it by checking a checksum this way:

WHEN MATCHED AND
     CHECKSUM(TARGET.Field1,TARGET.Field2, ... TARGET.Field25)
    <> 
     CHECKSUM(TARGET.Field1,TARGET.Field2, ... TARGET.Field25)
THEN UPDATE
SET FIELD1 = FIELD1

How to achieve the same in Oracle?

You can use MD5 to check if any value has changed. Periscope uses MD5 for tracking changes. — Fact, Mar 19 '18 at 00:21

score 7 · Accepted Answer · answered Mar 18 '18 at 21:01

Oracle's STANDARD_HASH function "computes a hash value for a given expression" (see the documentation). Use the checksums in the WHERE clause of the UPDATE (in the MERGE statement).

Tables for testing (Oracle 12c)

-- 2 tables
create table table1 as
select 1 id, 1 a1, 1 b1, 1 c1, 1 d1, 1 e1, 1 f1 from dual;

create table table2 as
select 2 id, 2 a2, 2 b2, 2 c2, 2 d2, 2 e2, 2 f2 from dual;

SHA256 checksum

-- eg
select
  standard_hash ( T.id || T.a1 || T.b1 || T.c1 || T.d1 || T.e1 || T.f1, 'SHA256' )
from table1 T ;

-- output
SHA256                                                            
2558A34D4D20964CA1D272AB26CCCE9511D880579593CD4C9E01AB91ED00F325

MERGE

merge into table1 T
  using (
    select id, a2, b2, c2, d2, e2, f2
    from table2
  ) T2 on ( T.id = T2.id ) 
  when matched then
    update
    set T.a1 = T2.a2 
      , T.b1 = T2.b2
      , T.c1 = T2.c2
      , T.d1 = T2.d2 
      , T.e1 = T2.e2 
      , T.f1 = T2.f2
    where 
      standard_hash ( T.id || T.a1 || T.b1 || T.c1 || T.d1 || T.e1 || T.f1, 'SHA256' )
      <>
      standard_hash ( T2.id || T2.a2 || T2.b2 || T2.c2 || T2.d2 || T2.e2 || T2.f2, 'SHA256' )
  when not matched then
    insert ( T.id, T.a1, T.b1, T.c1, T.d1, T.e1, T.f1 )
      values ( T2.id, T2.a2, T2.b2, T2.c2, T2.d2, T2.e2, T2.f2 )
    ;

-- 1 row merged

After the MERGE statement has been executed, the tables contain:

SQL> select * from table1;
ID  A1  B1  C1  D1  E1  F1  
1   1   1   1   1   1   1   
2   2   2   2   2   2   2   

SQL> select * from table2;
ID  A2  B2  C2  D2  E2  F2  
2   2   2   2   2   2   2

Modify table2 and MERGE again:

update table2
set a2 = 20, c2 = 30, f2 = 50
where id = 2 ;

insert into table2 ( id, b2, d2, e2 )
values (3, 33, 333, 3333 ) ;

select * from table2;
ID  A2  B2  C2  D2   E2    F2  
2   20  2   30  2    2     50  
3       33      333  3333

Execute the MERGE statement again. Table1 now contains:

SQL> select * from table1;
ID  A1  B1  C1  D1   E1    F1  
1   1   1   1   1    1     1   
2   20  2   30  2    2     50  
3       33      333  3333

Decide whether you need to use SHA256 over MD5. The additional protection against hash collisions comes at a cost. SHA256 takes about four times longer to calculate than MD5 (YMMV). For large data volumes that may matter greatly. — APC, Mar 19 '18 at 08:37

score 0 · Answer 2 · answered Mar 18 '18 at 20:07

0

Try with ORA_HASH, such as this example:

when matched and ora_hash(scol1 || scol2 || scol3 || ... || scol25) <>
                 ora_hash(...)
then update set
  field1 = field2

|| is the concatenation operator.

answered Mar 18 '18 at 20:07

Littlefoot

131,892
15
35
57

How to calculate checksum in Oracle?

2 Answers2