checksum of a column

Something I always wanted arrived this week, a checksum of a column !


SQL> create table t1(x number);
Table created.
SQL> create table t2(x number);
Table created.
SQL> insert into t1(x) values (1);
1 row created.
SQL> insert into t2(x) values (1);
1 row created.
SQL> select
(select checksum(x) from t1)t1,
(select checksum(x) from t2)t2
from dual;

        T1         T2
---------- ----------
    863352     863352

SQL> insert into t1(x) values (2);
1 row created.
SQL> select
(select checksum(x) from t1)t1,
(select checksum(x) from t2)t2
from dual;

        T1         T2
---------- ----------
    778195     863352

it is much more convenient than minus / intersect / not in and others to find out if two columns have identical values.

Oracle Database 21c which has just been released on Linux have a few more SQL improvement, like MINUS ALL that deals with duplicates and BIT_AND_AGG (OR, XOR) to aggregate bits.


SQL> select
2 EMPNO,
3 replace(
4 replace(
5 replace(
6 replace(
7 replace(
8 replace(
9 replace(
10 replace(
11 replace(
12 replace(
13 replace(
14 replace(
15 replace(
16 replace(
17 replace(
18 replace(to_char(empno, 'FMXXXX'),
19 '0', '0000'),
20 '1', '0001'),
21 '2', '0010'),
22 '3', '0011'),
23 '4', '0100'),
24 '5', '0101'),
25 '6', '0110'),
26 '7', '0111'),
27 '8', '1000'),
28 '9', '1001'),
29 'A', '1010'),
30 'B', '1011'),
31 'C', '1100'),
32 'D', '1101'),
33 'E', '1110'),
34 'F', '1111') BIN
35 from scott.emp
36 /

     EMPNO BIN
---------- ----------------
      7369 0001110011001001
      7499 0001110101001011
      7521 0001110101100001
      7566 0001110110001110
      7654 0001110111100110
      7698 0001111000010010
      7782 0001111001100110
      7788 0001111001101100
      7839 0001111010011111
      7844 0001111010100100
      7876 0001111011000100
      7900 0001111011011100
      7902 0001111011011110
      7934 0001111011111110

14 rows selected.


SQL> select
2 bit_and_agg(empno) EMPNO,
3 replace(
4 replace(
5 replace(
6 replace(
7 replace(
8 replace(
9 replace(
10 replace(
11 replace(
12 replace(
13 replace(
14 replace(
15 replace(
16 replace(
17 replace(
18 replace(to_char(bit_and_agg(empno), 'FMXXXX'),
19 '0', '0000'),
20 '1', '0001'),
21 '2', '0010'),
22 '3', '0011'),
23 '4', '0100'),
24 '5', '0101'),
25 '6', '0110'),
26 '7', '0111'),
27 '8', '1000'),
28 '9', '1001'),
29 'A', '1010'),
30 'B', '1011'),
31 'C', '1100'),
32 'D', '1101'),
33 'E', '1110'),
34 'F', '1111') BIN
35 from scott.emp
36 /

     EMPNO BIN
---------- ----------------
      7168 0001110000000000


SQL> select
2 bit_or_agg(empno) EMPNO,
3 replace(
4 replace(
5 replace(
6 replace(
7 replace(
8 replace(
9 replace(
10 replace(
11 replace(
12 replace(
13 replace(
14 replace(
15 replace(
16 replace(
17 replace(
18 replace(to_char(bit_or_agg(empno), 'FMXXXX'),
19 '0', '0000'),
20 '1', '0001'),
21 '2', '0010'),
22 '3', '0011'),
23 '4', '0100'),
24 '5', '0101'),
25 '6', '0110'),
26 '7', '0111'),
27 '8', '1000'),
28 '9', '1001'),
29 'A', '1010'),
30 'B', '1011'),
31 'C', '1100'),
32 'D', '1101'),
33 'E', '1110'),
34 'F', '1111') BIN
35 from scott.emp
36 /


     EMPNO BIN
---------- ----------------
      8191 0001111111111111

It obviously works