Hi Tom,

We have a data warehouse with more than 1000 tables and incremental data is loaded into each table by comparing the hash key of previous snapshot data with the hash key of new snapshot. We are using DBMS_CRYPTO.HASH method to compute the hash key. The hash key is added as a generated column to the table and DBMS_CRYPTO.HASH is placed inside a function as follows.

CREATE TABLE FOO
(
col1 ..,
col2 ..,
...
HASH_KEY VARCHAR2(4000) GENERATED ALWAYS AS COMPUTE_HASH_UDF(TO_CHAR(COL1)||TO_CHAR(COL2)..)
)

CREATE OR REPLACE FUNCTION COMPUTE_HASH_UDF(IN_REC IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC PARALLEL IS
BEGIN
RETURN TO_CHAR(DBMS_CYPTO.HASH(...))
END COMPUTE_HASH_UDF

With Oracle 12c, I have observed that standard_hash function computes the hash much faster than DBMS_CRYPTO.HASH. However, standard_hash doesn't seem to work inside function using a simple return statement when given as follows

return RAWTOHEX(STANDARD_HASH(....))

It works only as follows.

SELECT RAWTOHEX(STANDARD_HASH(....)) INTO X;
RETURN X;

This, however slows down the performance of my function. Is there anyway I can use standard_hash inside the function without compromising on performance?

--
-- old
--
SQL> create or replace
  2  function COMPUTE_HASH_UDF(i varchar2) return varchar2 deterministic is
  3  begin
  4    return 'x';  -- just so we have a function
  5  end;
  6  /

Function created.

SQL>
SQL> CREATE TABLE t
  2  (
  3  col1 int,
  4  col2 int,
  5  HASH_KEY VARCHAR2(4000) GENERATED ALWAYS AS ( COMPUTE_HASH_UDF(TO_CHAR(COL1)||TO_CHAR(COL2)) )
  6  );

Table created.

--
-- new
--
SQL> CREATE TABLE t1
  2  (
  3  col1 int,
  4  col2 int,
  5  HASH_KEY VARCHAR2(4000) GENERATED ALWAYS AS ( standard_hash(TO_CHAR(COL1)||TO_CHAR(COL2)) )
  6  );

Table created.