CHECKSUM function in Oracle 21c, be aware of these surprises

 In Oracle 21c, CHECKSUM was added as a new analytical function, with below purpose stated in Oracle documentation:

Use CHECKSUM to detect changes in a table. The order of the rows in the table does not affect the result.

Let's try it out.

Setup the testing data. These 2 tables purposely populated with different datasets.

create table t1 (id number, c1 varchar2(10));
insert into t1 values (1,'a');
insert into t1 values (2,'b');
commit;

create table t2 (id number, c1 varchar2(10));
insert into t2 values (1,'b');
insert into t2 values (2,'a');
commit;

SQL> select * from t1;

   ID    C1
_____ _____
    1 a
    2 b

SQL> select * from t2;

   ID    C1
_____ _____
    1 b
    2 a

Finding 1: regardless the data setup is NOT the same, the check is same, as checksum computed without considering data ordering.

SQL> select checksum(id),checksum(c1) from t1;

   CHECKSUM(ID)    CHECKSUM(C1)
_______________ _______________
         778195            3783

SQL> select checksum(id),checksum(c1) from t2;

   CHECKSUM(ID)    CHECKSUM(C1)
_______________ _______________
         778195            3783

Finding 2: if the data not same regardless ordering, the checksum will be different.

SQL> update t2 set c1='A' where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select checksum(id),checksum(c1) from t2;

   CHECKSUM(ID)    CHECKSUM(C1)
_______________ _______________
         778195          585598

Finding 3: checksum return "0" for some test cases, below is one example.

Observation: pair of repeating values generated value "0", canceling the effect of checksum. For example, three values 'a' have the same effect as single value 'a'

SQL> insert into t1 values (1,'a');

1 row inserted.

SQL> insert into t1 values (2,'b');

1 row inserted.

SQL> commit;

Commit complete.

SQL> select * from t1;

   ID    C1
_____ _____
    1 a
    2 b
    1 a
    2 b


SQL> select checksum(id),checksum(c1) from t1;

   CHECKSUM(ID)    CHECKSUM(C1)
_______________ _______________
              0               0