oracle 21c 新特性之 CHECKSUM 分析函式

微信公眾號： 老杜隨筆
關注可瞭解更多的文章。問題或建議，請公眾號留言;

Oracle 21c 引入了一個新的分析函式 CHECKSUM，它可以為一個或多個列計算校驗和，用於檢測資料的完整性，這個函式可以用於替代 DBMS_SQLHASH.GETHASH 函式。也可以用於檢查表的內容是否已變更。

一、初始化示例資料

SQL> CREATE TABLE employees 

  2  ( employee_id NUMBER PRIMARY KEY,    

  3    first_name VARCHAR2(50),   

  4   last_name VARCHAR2(50),    

  5   email VARCHAR2(100),    

  6  hire_date DATE,    

  7  salary NUMBER,

  8  deptno   number(2)

       );



Table created.

-- 插入樣本資料

SQL> INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, deptno  )

  2  VALUES (1, 'John', 'Doe', 'john.doe@example.com', TO_DATE('2022-01-01', 'YYYY-MM-DD'), 50000, 1);



1 row created.



SQL> 

SQL> INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, deptno  )

  2  VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', TO_DATE('2022-02-15', 'YYYY-MM-DD'), 60000, 2);



1 row created.



SQL> 

SQL> INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, deptno  )

  2  VALUES (3, 'Mike', 'Johnson', 'mike.johnson@example.com', TO_DATE('2022-03-20', 'YYYY-MM-DD'), 55000,2);



1 row created.



SQL> commit 

  2  ;



Commit complete.

二、CHECKSUM 聚合函式

CHECKSUM 函式返回一個8位元組有符號長整型校驗和，並轉換為Oracle number 型別。這對於檢查表的內容是否已更改非常有用。校驗和基於一組行的傳入表示式，該表示式不受行順序的影響。表示式可以是列、常量、繫結變數或組合它們的表示式。它支援除ADT和JSON之外的大多數資料型別。可以選擇對所有行或不同的行執行操作。
作為聚合函式，它減少了行數，因此稱為“聚合”。

SQL> select checksum(first_name ) as row_checksum from   employees ; 



ROW_CHECKSUM

------------

      775823

透過 GROUP BY 子句，獲得更細粒度的資訊。在下面的示例中，可以對部門的薪水求校驗和。

SQL> select deptno   ,      

  2   checksum(salary ) as checksum_id from  

  3   employees  group by deptno   order by deptno   ;



    DEPTNO CHECKSUM_ID

---------- -----------

         1      612421

         2      756575

向表加增加一行部門編號為“1”的資料，檢視它如何影響校驗和，並刪除。

SQL> INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary, deptno  )

  2  VALUES (4, 'DU', 'XI', 'du@example.com', TO_DATE('2023-01-01', 'YYYY-MM-DD'), 60000, 1);



1 row created.



SQL> 

SQL> select deptno   ,      

  2      checksum(salary ) as checksum_id from  

  3      employees  group by deptno   order by deptno   ;



    DEPTNO CHECKSUM_ID

---------- -----------

         1      722294

         2      756575

SQL> select checksum(distinct salary ) as checksum_dept from   employees  ; 



CHECKSUM_DEPT

-------------

       519258

看到這會影響表的整體校驗和，並且按部門編號分組檢視他其實隻影響 group by 查詢中部門編號為“1”的校驗和。

三、CHECKSUM 分析函式

CHECKSUM 分析函式的基本定義描述如下。

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

顯示薪水校驗和以及所有原始資料。

SQL> select employee_id, first_name,      deptno,      salary,   checksum(salary) over () as checksum_total from   employees;



EMPLOYEE_ID FIRST_NAME                                    DEPTNO     SALARY CHECKSUM_TOTAL

----------- --------------------------------------------- ------ ---------- --------------

          1 John                                               1      50000         752745

          2 Jane                                               2      60000         752745

          3 Mike                                               2      55000         752745

          4 DU                                                 1      60000         752745

需要注意的是，CHECKSUM 函式返回的是一個數字，如果列的值發生變化，即使是微小的變化，也可能導致校驗和的變化。因此，CHECKSUM 不適合用於密碼學上的校驗和，它更適合用於快速比較或建立識別符號。