8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
CHECKSUM Analytic Function
This article gives an overview of the CHECKSUM analytic function introduced in Oracle database 21c. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Related articles.
Setup
The examples in this article require the following table.
--drop table emp purge;
create table emp (
empno number(4) constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);
insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;
CHECKSUM as an Aggregate Function
The CHECKSUM function returns a deterministic 8-byte signed long checksum, converted to an Oracle number. This can be useful to check the contents of a table to see if it has changed. The checksum is based on the passed in expression of a group of rows, which is not affected by the row order. The expression can be a column, constant, bind variable, or an expression combining them. It supports most data types except ADT and JSON. We have the choice of performing the action for all rows, or distinct rows.
As an aggregate function it reduces the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated values.
select checksum(sal) as checksum_total
from emp;
CHECKSUM_TOTAL
--------------
251201
SQL>
The DISTINCT, UNIQUE and ALL keywords are also available for the analytic function.
We can get more granularity of information by including a GROUP BY clause. In the following example we see the checksum of the salaries per department.
select deptno,
checksum(sal) as checksum_dept
from emp
group by deptno
order by deptno;
DEPTNO CHECKSUM_DEPT
---------- -------------
10 47845
20 350390
30 838098
SQL>
In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.
We add a row to department "10", see how it affects the checksums, and rollback the change.
insert into emp (empno, ename, sal, deptno) values (9999, 'HALL', 1000, 10);
select checksum(distinct sal) as checksum_dept
from emp;
CHECKSUM_DEPT
-------------
826243
SQL>
select deptno,
checksum(sal) as checksum_by_dept
from emp
group by deptno
order by deptno;
DEPTNO CHECKSUM_BY_DEPT
---------- ----------------
10 345922
20 350390
30 838098
SQL>
rollback;
We can see this affects the overall checksum of the table, and only the checksum of department "10" in the group by query.
Using DISTINCT or UNIQUE keywords mean only unique values in the expression are used for the calculation. The ALL keyword is the same as the default action.
-- ALL : The default action.
select checksum(sal) as checksum_total,
checksum(all sal) as checksum_total_all
from emp;
CHECKSUM_TOTAL CHECKSUM_TOTAL_ALL
-------------- ------------------
251201 251201
SQL>
-- DISTINCT or UNIQUE values.
select checksum(distinct sal) as checksum_total_distinct,
checksum(unique sal) as checksum_total_unique
from emp;
CHECKSUM_TOTAL_DISTINCT CHECKSUM_TOTAL_UNIQUE
----------------------- ---------------------
216548 216548
SQL>
The DISTINCT, UNIQUE and ALL keywords are also available for the analytic function.
CHECKSUM Analytic Function
The basic description for the CHECKSUM analytic function is shown below. The analytic clause is described in more detail here.
CHECKSUM "(" [ DISTINCT | ALL ] expr ")" [ OVER "(" analytic_clause ")" ]
Omitting a partitioning clause from the OVER clause means the whole result set is treated as a single partition. In the following example we display the salary checksum, as well as all the original data.
select empno,
ename,
deptno,
sal,
checksum(sal) over () as checksum_total
from emp;
EMPNO ENAME DEPTNO SAL CHECKSUM_TOTAL
---------- ---------- ---------- ---------- --------------
7369 SMITH 20 800 376934
7499 ALLEN 30 1600 376934
7521 WARD 30 1250 376934
7566 JONES 20 2975 376934
7654 MARTIN 30 1250 376934
7698 BLAKE 30 2850 376934
7782 CLARK 10 2450 376934
7788 SCOTT 20 3000 376934
7839 KING 10 5000 376934
7844 TURNER 30 1500 376934
7876 ADAMS 20 1100 376934
7900 JAMES 30 950 376934
7902 FORD 20 3000 376934
7934 MILLER 10 1300 376934
9999 HALL 10 1000 376934
SQL>
Adding the partitioning clause allows us to return the checksum within a partition.
select empno,
ename,
deptno,
sal,
checksum(sal) over (partition by deptno) as checksum_by_dept
from emp;
EMPNO ENAME DEPTNO SAL CHECKSUM_BY_DEPT
---------- ---------- ---------- ---------- ----------------
7782 CLARK 10 2450 345922
9999 HALL 10 1000 345922
7839 KING 10 5000 345922
7934 MILLER 10 1300 345922
7566 JONES 20 2975 350390
7369 SMITH 20 800 350390
7788 SCOTT 20 3000 350390
7902 FORD 20 3000 350390
7876 ADAMS 20 1100 350390
7844 TURNER 30 1500 838098
7499 ALLEN 30 1600 838098
7900 JAMES 30 950 838098
7521 WARD 30 1250 838098
7654 MARTIN 30 1250 838098
7698 BLAKE 30 2850 838098
SQL>
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
Hope this helps. Regards Tim...