Oracle hashkey实现为虚拟列

ASKTOM 2020-03-06

271

问题描述

首先，我想说的是，ASK TOM是关于Oracle数据库问题的唯一最大资源。多年来，我从该网站中学到了很多有价值的东西，我只想感谢您的努力。

我的问题是关于实现哈希值以促进避免丢失更新。我见过的所有示例都将其创建为插入/更新行时维护的 “真实” 列。我想知道是否有任何缺点将其实现为虚拟列，例如在下面的脚本中。我特别想知道虚拟列的性能问题。如果我没有在查询中选择虚拟列，是否会从中产生任何开销？

CREATE OR REPLACE PACKAGE my_security_pkg
AS
   FUNCTION hash_value(
      p_input_string_in IN VARCHAR2
      )
   RETURN VARCHAR2 DETERMINISTIC;
END my_security_pkg;
/

CREATE OR REPLACE PACKAGE BODY my_security_pkg
AS
   FUNCTION hash_value(
      p_input_string_in IN VARCHAR2
      )
   RETURN VARCHAR2 DETERMINISTIC
   IS
   BEGIN
      RETURN DBMS_CRYPTO.HASH(UTL_I18N.string_to_raw(p_input_string_in),DBMS_CRYPTO.hash_sh512);
   END hash_value;
END my_security_pkg;
/

DROP TABLE my_emp;
/

CREATE TABLE my_emp
AS( SELECT * FROM SCOTT.emp )
/

ALTER TABLE my_emp
ADD hashkey      VARCHAR2(4000)
GENERATED ALWAYS AS(
   CAST(MY_SECURITY_PKG.hash_value(TO_CHAR(empno) || '|' || ename || '|' || job || '|' || TO_CHAR(mgr) || '|' || TO_CHAR(hiredate, 'mm/dd/yyyy') || '|' || TO_CHAR(sal) || '|' || TO_CHAR(comm) || '|' || TO_CHAR(deptno))
   AS VARCHAR2(512))
   );
/复制

专家解答

谢谢!

If I do not select the virtual column in my query, do I incur any overhead from it?

没有。数据库仅在您访问它时对其进行评估。所以如果你不选择它，它就不会被执行。

您可以通过在您的函数中添加dbms_output (或类似的) 调用来看到这一点:

CREATE OR REPLACE PACKAGE BODY my_security_pkg
AS
   FUNCTION hash_value(
      p_input_string_in IN VARCHAR2
      )
   RETURN VARCHAR2 DETERMINISTIC
   IS
   BEGIN
      dbms_output.put_line ( 'CALLED!' );
      RETURN DBMS_CRYPTO.HASH(UTL_I18N.string_to_raw(p_input_string_in),DBMS_CRYPTO.hash_sh512);
   END hash_value;
END my_security_pkg;
/

select * from my_emp
where  empno = 7369;

EMPNO   ENAME   JOB     MGR    HIREDATE               SAL   COMM     DEPTNO   HASHKEY                                                                                                                            
    7369 SMITH    CLERK       7902 17-DEC-1980 00:00:00       800            20 FB376528D96E948CC31460B5508200AB2E86EB3EC98F355A4967F8E034F3206356A044B80960A0ED680A336E613BF2AA55C1ACC190DB084094344E28333082A4    

CALLED!

select ename from my_emp
where  empno = 7369;

ENAME   
SMITH   复制

所以在这方面是有效的。

缺点是每次选择它时都必须对其进行评估。通常你查询数据的次数比你写的更多。对于您反复调用的昂贵函数，这可以加起来。

虚拟列的一个很大的好处是它永远不会与其他列不同步。所以不需要触发器/约束/等。为了确保人们在更新其他列时总是更新它。

您只需要在人们可以更改数据的表单中选择哈希值。通常是您将对表运行的查询的一小部分。

所以就我个人而言，我会从一个虚拟专栏开始。如果性能开销太大，请切换到真实的。