CHECKSUM Function in Oracle 21c

In version 12c, Oracle introduced the STANDARD_HASH function, which allows you to calculate the hash value of a field or expression:

SQL> select id,salary,substr(STANDARD_HASH(id||salary),1,20) hash_id_sal from tbl1;
        ID     SALARY HASH_ID_SAL
---------- ---------- ----------------------
         1         10 5E796E48332AF4142B10
         2         12 E2154FEA5DA2DD0D1732
         3         17 F44A286F486D11990238
         4         18 93AC1946CB917ABC4735

This method allows us to detect changes in table row values. In earlier versions before 12c, we could use other functions like ORA_HASH for this purpose:

SQL> select id,salary,ora_hash(id||salary)  hash_id_sal from tbl1;
        ID     SALARY HASH_ID_SAL
---------- ---------- -----------
         1         10  3316966336
         2         12  1402677848
         3         17  3795753203
         4         18   936769390

In version 21c, a new feature was introduced: the CHECKSUM function, which enables data change detection at the column level.

With this function, you can create a checksum for each column so that if any field in that column changes, you can detect it using the generated checksum. However, if values are swapped between rows within the same column, the checksum value will not change!

To better understand this function, consider the following data:

SQL> select * from tbl1;
        ID NAME               SALARY
---------- -------------- ----------
         1 vahid                  10
         2 ali                    12
         3 sima                   17
         4 nima                   18

To calculate the checksum of the id and salary columns:

SQL> select checksum(id),checksum(salary) CH_SAL from tbl1;
CHECKSUM(ID)  CH_SAL
------------ ----------
      496078     567030

Even a small change in the above data will change the CHECKSUM result:

SQL> update tbl1 set salary=25 where id=4;
1 row updated
SQL> select checksum(salary) ch_id_sal from tbl1;
CH_ID_SAL
----------
    131164

However, if two values in one column are swapped, the CHECKSUM result for that column will remain the same:

SQL> select id,salary from tbl1;
        ID     SALARY
---------- ----------
         1         10
         2         12
         3         17
         4         25
SQL> declare
  2  v_sal_1 number;
  3  v_sal_2 number;
  4  begin
  5  select salary into v_sal_1 from tbl1 where id=1;
  6  select salary into v_sal_2 from tbl1 where id=2;
  7  update tbl1 set salary=v_sal_2 where id=1;
  8  update tbl1 set salary=v_sal_1 where id=2;
  9  commit;
 10  end;
 11  /
PL/SQL procedure successfully completed
SQL> select id,salary from tbl1;
        ID     SALARY
---------- ----------
         1         12
         2         10
         3         17
         4         25
SQL> select checksum(salary) ch_id_sal from tbl1;
 CH_ID_SAL
----------
    131164

If for each value in a column there exists an identical counterpart (e.g., four 1s and four 2s), the CHECKSUM function will return 0. But if the count of values like 1 is odd, the checksum will return a non-zero number:

SQL> select salary from tbl1;
    SALARY
----------
         1
         2
         1
         2
         1
         2
6 rows selected
SQL> select checksum(salary) from tbl1;
CHECKSUM(SALARY)
----------------
          778195
SQL> insert into tbl1 values(7,'reza',1);
1 row inserted
SQL> insert into tbl1 values(8,'darush',2);
1 row inserted
SQL> select salary from tbl1;
    SALARY
----------
         1
         2
         1
         2
         1
         2
         1
         2
8 rows selected
SQL> select checksum(salary) from tbl1;
CHECKSUM(SALARY)
----------------
               0

In such cases, you can use the DISTINCT keyword:

SQL> select checksum(salary),checksum(distinct salary) from tbl1;
CHECKSUM(SALARY) CHECKSUM(DISTINCTSALARY)
---------------- ------------------------
               0                   778195