8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

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.

AVG * CHECKSUM * CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID
CLUSTER_SET CORR * COUNT * COVAR_POP * COVAR_SAMP *
CUME_DIST DENSE_RANK FEATURE_DETAILS FEATURE_ID FEATURE_SET
FEATURE_VALUE FIRST FIRST_VALUE * KURTOSIS_POP * KURTOSIS_SAMP *
LAG LAST LAST_VALUE * LEAD LISTAGG
MATCH_RECOGNIZE MAX * MEDIAN MIN * NTH_VALUE *
NTILE PERCENT_RANK PERCENTILE_CONT PERCENTILE_DISC PREDICTION
PREDICTION_COST PREDICTION PREDICTION_COST PREDICTION_DETAILS PREDICTION_PROBABILITY
PREDICTION_SET RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions * ROW_NUMBER
SKEWNESS_POP * SKEWNESS_SAMP * STDDEV * STDDEV_POP * STDDEV_SAMP *
SUM * VAR_POP * VAR_SAMP * VARIANCE * String Aggregation
Top-N Queries  

For more information see:

Hope this helps. Regards Tim...

Back to the Top.