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...