📋Steps in this guide1/4

CHECKSUM Analytic Function

This article gives an overview of the CHECKSUM analytic function introduced in Oracle database 21c.

oracle 21cconfigurationintermediate

by OracleDba

13 views

Setup

The examples in this article require the following table.

Code/Command (click line numbers to comment):

--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 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 table to a single row with the aggregated values. The , and keywords are also available for the analytic function. We can get more granularity of information by including a clause. In the following example we see the checksum of the salaries per department. 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. We can see this affects the overall checksum of the table, and only the checksum of department "10" in the group by query. Using or keywords mean only unique values in the expression are used for the calculation. The keyword is the same as the default action. The , and keywords are also available for the analytic function.

Code/Command (click line numbers to comment):

select checksum(sal) as checksum_total
from   emp;

CHECKSUM_TOTAL
--------------
        251201

SQL>

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>

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;

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

CHECKSUM Analytic Function

The basic description for the analytic function is shown below. The analytic clause is described in more detail here . Omitting a partitioning clause from the 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. Adding the partitioning clause allows us to return the checksum within a partition.

Code/Command (click line numbers to comment):

CHECKSUM "(" [ DISTINCT | ALL ] expr ")" [ OVER "(" analytic_clause ")" ]

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>

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: - CHECKSUM - Analytic Functions : All Articles - DBMS_SQLHASH Package Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!