Oracle CHECKSUM Function

Has the data in a table changed since yesterday? Is the copy in the test database the same as production? Comparing row by row is slow and needs both copies in one place. CHECKSUM computes a single number from the values of a column over a group; the same values give the same checksum in any order, and a change almost always gives a different one.

Code for This Guide

The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

checksum([all | distinct] expr)

CHECKSUM is an aggregate: it returns one number per group. DISTINCT computes the checksum over the different values only.

Checksums of Columns

Example:

select checksum(airport_code) as codes, checksum(time_zone) as zones, count(*) as airports
from   airports;

select checksum(distinct country_code) as distinct_countries from airports;

Output:

   CODES     ZONES    AIRPORTS
________ _________ ___________
   12844    508375          22

   DISTINCT_COUNTRIES
_____________________
               570930

Each column gets its own checksum. Save them, and compare later or in another database to find out whether anything changed.

Order Does Not Matter, Values Do

Example:

-- the same values in another order give the same checksum; one change gives another
select checksum(v) as original from (values ('DXB'), ('LHR'), ('SIN')) t (v);
select checksum(v) as reordered from (values ('SIN'), ('DXB'), ('LHR')) t (v);
select checksum(v) as changed from (values ('DXB'), ('LHR'), ('SYD')) t (v);

Output:

   ORIGINAL
___________
     826913

   REORDERED
____________
      826913

   CHANGED
__________
     38779

The same three codes in another order give the same checksum, while replacing one code gives a different number. Rows come back in no guaranteed order, so this independence from order is what makes CHECKSUM usable for comparing tables.

Things to Know

• Combine columns into one expression, such as a || '|' || b, to checksum whole rows.
• Use GROUP BY to checksum each partition or day separately, so a difference points to where the data changed.
• A checksum is not a cryptographic hash; different data can, rarely, give the same value. Use STANDARD_HASH for security purposes.

Related Guides

• Oracle BIT_AND_AGG Function

Conclusion

CHECKSUM returns one number for the values of a column over a group, independent of their order. Compute it per column or per group, store it, and compare it to detect changes between runs or databases without moving the data.