{"id":1205,"date":"2020-03-03T10:00:34","date_gmt":"2020-03-03T08:00:34","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1205"},"modified":"2020-03-22T18:22:00","modified_gmt":"2020-03-22T16:22:00","slug":"building-hash-keys-using-sql-macros-in-oracle-20c","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/building-hash-keys-using-sql-macros-in-oracle-20c\/","title":{"rendered":"Building Hash Keys using SQL Macros in Oracle 20c"},"content":{"rendered":"\n

In the next post about SQL macros in Oracle 20c we look at how they could be useful for building hash keys. If you are familiar with Data Warehousing and Data Vault<\/a> modelling approach, you will probably know why it can be a good idea to build hash keys or hash diffs. Anyway, we will not discuss whether or not you should use them, but rather how you can do this in Oracle in a consistent and performant way.<\/p>\n\n\n\n\n\n\n\n

There are several ways to generate a hash key in Oracle. The fastest one is by using a SQL function STANDARD_HASH<\/strong> which has been introduced in Oracle 12.1. For more details see the blog post of my colleague Dani Schnider<\/a>. <\/p>\n\n\n\n

On the other hand it is very important to establish a set or rules to follow while building a hash key, especially in case of multi-column keys or even hashing all columns of a data row to produce a hash diff. For example you have to cast all data types to string using the same format mask, concatenate all strings using the same delimiter an so on. <\/p>\n\n\n\n

A proven idea is to encapsulate these rules in a PL\/SQL procedure, that should be used by everyone in your project to build hash keys. In the past, it was not so easy to make such a procedure generic though. Since Oracle 18c we could try to do it in a flexible way using polymorphic table functions (PTF). But there is still a problem with this approach. <\/p>\n\n\n\n

STANDARD_HASH is only available in SQL. You cannot call it directly in PL\/SQL. This is still true in 20c. The only way is doing a SELECT FROM DUAL. But doing SELECT out of PTF will cause the infamous context switch!<\/p>\n\n\n\n

So we need to encapsulate the rule set in the PL\/SQL procedure, while remaining in a SQL engine. It’s where SQL macros introduced in 20c can help, I guess.<\/p>\n\n\n\n

Let’s take an EMP table as an example and look at what we want to achieve and what the problem is. We’re using a function STANDARD_HASH to build a MD5-hash of a primary key and of the whole row, which means all fields converted to string and concatenated with a defined delimiter. Note that I formatted the values so that you see only a narrower part or it. <\/p>\n\n\n

\nSQL> SELECT STANDARD_HASH(TO_CHAR(empno),'MD5') as hash_key\n,      STANDARD_HASH(TO_CHAR(empno)||'#'||ename||'#'||job||'#'||\n                     TO_CHAR(mgr)||'#'||TO_CHAR(hiredate,'YYYYMMDD')\n                     ||'#'||TO_CHAR(sal)||'#'||TO_CHAR(comm)\n                     ||'#'||TO_CHAR(deptno), 'MD5') as hash_diff\n,      e.*\nFROM   emp e\nFETCH FIRST 3 ROWS ONLY\n\nHASH_KEY   HASH_DIFF       EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO\n---------- ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------\n0D7F9017FB 42CB6932B4       7369 SMITH      CLERK           7902 17-DEC-80        800                    20\n7A2B33C672 AA63299F72       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30\nE1E1F667CE 27332DD16B       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30\n\n3 rows selected.\n<\/pre><\/div>\n\n\n
Listing 1: Manually build a hash key and a hash diff<\/em><\/strong><\/p>\n\n\n\n
It is obvious that if you have a concatenated key or in case of hash diffs, the expressions to be hashed become so complex that they can hardly be handled manually. Imagine what it looks like with tables of few hundreds of columns! You need a generator (Data Warehouse Automation tool, like  BiGENiUS<\/a> for example) to generate this code. If you don’t have one, you can still generate only a hardest part of such SQL statement – with a SQL macro! <\/p>\n\n\n\n
Implementation with SQL macro<\/h4>\n\n\n\n
Now what kind of macro should we try: scalar or table? At first sight, we have to return scalar values – hash_key and hash_diff – so why not use scalar macro? But note that we than have to pass all the columns as parameter to build a hash diff. Again, think of a table with few hundred columns \ud83d\ude09 Such macro will be table specific, that means you just need another macro with different parameters for another table. And last but not least, to be able to apply string conversion rules, we have to get the data type information for every column, likely querying the data dictionary.  <\/p>\n\n\n\n
Table macros, on the other hand, don’t have those disadvantages and can be made really generic. <\/p>\n\n\n\n
Table is the default type for SQL macros, so you don’t have to specify the type in the function annotation. Table macros can only appear in a FROM clause.  So what we want to implement, is a SQM function which can be queried as follows and will pass all source columns  through adding two new columns HASH_KEY and HASH_DIFF:<\/p>\n\n\n
\nSELECT e.hash_key\n,      e.hash_diff\n,      e.*\nFROM   add_hash_columns (emp) e;\n   \n<\/pre><\/div>\n\n\n
Listing 2: The desired way to use a SQL macro table function<\/em><\/strong><\/p>\n\n\n\n
The key difference is that table macros can accept table parameters<\/strong>. You may know what table parameters are, if you are already familiar with Polymorphic Table Functions (PTF)<\/a> which have been introduced in Oracle 18c. I have also blogged a lot about PTF<\/a>. A PTF must have exactly one parameter of type TABLE. For use in DESCRIBE method this will be implicitly converted in record type defined in DBMS_TF package: DBMS_TF.TABLE_T. With SQL macros you cannot define arguments of type TABLE but instead you can directly use DBMS_TF.TABLE_T. Passing a table name to a macro function leads to implicit conversion to that record type.<\/p>\n\n\n\n
Let’s start with a macro to build a hash diff only. For now, all we need is just one table parameter of the type DBMS_TF.TALE_T.  The DBMS_TF.TABLE_T record contains all information we need: all columns with their data types as long as the table name. How exactly do we get at it? Please note that the documentation of DBMS_TF.TABLE_T<\/a> is not correct as of now. You can refer the package specification in the database itself, where you’ll find the table_name as an atribute:<\/p>\n\n\n
\n  TYPE TABLE_T          IS RECORD\n  (\n    column                TABLE_COLUMNS_T,     \/* Column information *\/\n    schema_name           dbms_quoted_id,  \/* the schema name OF ptf *\/\n    package_name          dbms_quoted_id, \/* the package name OF ptf *\/\n    ptf_name              dbms_quoted_id,    \/* the ptf name invoked *\/\n    table_schema_name     dbms_quoted_id,       \/* schema name table *\/\n    table_name            dbms_quoted_id               \/* table name *\/\n  );\n<\/pre><\/div>\n\n\n
Listing 3: the definition or the record DBMS_TF.TABLE_T<\/em><\/strong><\/p>\n\n\n\n
The field column<\/em><\/strong> is of type TABLE_COLUMNS_T<\/strong> which is a table of the type COLUMN_T<\/strong>, which is turn defined as:<\/p>\n\n\n
\nTYPE COLUMN_T          IS RECORD\n  (\n    description            COLUMN_METADATA_T, \/* Column metadata *\/\n    pass_through           BOOLEAN,      \/* Pass-through column? *\/\n    for_read               BOOLEAN  \/* Column data will be read? *\/\n  );\n<\/pre><\/div>\n\n\n
Listing 4: record type COLUMN_T<\/em><\/strong><\/p>\n\n\n\n
 Next we look at the the field description<\/em><\/strong> and what its datatype  COLUMN_METADATA_T<\/strong> means (Listing 5). Here we find the information we need to cast actual data types a strings.<\/p>\n\n\n
\nTYPE COLUMN_METADATA_T IS RECORD\n  (\n    type               PLS_INTEGER DEFAULT TYPE_VARCHAR2,\n    max_len            PLS_integer DEFAULT -1,\n    name               VARCHAR2(32767),\n    name_len           PLS_INTEGER,\n    \/* following two attributes are used for numerical data *\/\n    precision          PLS_INTEGER,\n    scale              PLS_INTEGER,\n    \/* following three attributes are used for character data *\/\n    charsetid          PLS_INTEGER,\n    charsetform        PLS_INTEGER,\n    collation          PLS_INTEGER,\n    \/* following attributes may be used in future *\/\n    schema_name        DBMS_QUOTED_ID,\n    schema_name_len    PLS_INTEGER,\n    type_name          DBMS_QUOTED_ID,\n    type_name_len      PLS_INTEGER\n  );\n<\/pre><\/div>\n\n\n
Listing 5: record type COLUMN_METADATA_T<\/em><\/strong><\/p>\n\n\n\n
 Now we know all the ingredients and can implement the function (Listing 6). Let inspect the code:<\/p>\n\n\n\n
  •  line 9 – we’re building hash diff of the whole row, so we iterate through all columns<\/li>
  • line 10 –  the name of the column is in  t.column(i).description.name<\/strong><\/em> <\/li>
  • lines 11, 13, 15 – t.column(i).description.type<\/strong><\/em> that is the way we get the data type of particular column that we compare to the constants defined in DBMS_TF<\/li>
  • lines 12, 14, 16, 18 – continue to build a string to be hashed bearing in mind the rules for converting different data types, using delimiter and so on<\/li>
  • line 21 – build and return the expression for then STANDARD_HASH call along with selecting all other columns from the table (we have the table name in <\/s>t.table_name<\/s><\/em><\/strong>)<\/li><\/ul>\n\n\n\n
    UPDATE 22.03.2020: We don’t actually have to concatenate the table name as in  line 21 at the end of the Listing 6. See the update at the end of this post<\/strong><\/p><\/blockquote>\n\n\n
    \nCREATE OR REPLACE FUNCTION add_hash_columns(t DBMS_TF.TABLE_T) \nRETURN VARCHAR2 SQL_MACRO(TABLE)\nAS\n v_hdiff clob ;\n v_str   varchar2(200);\n v_delimiter varchar2(9):= '||''#''||';\n v_name dbms_id;\nBEGIN\n  FOR I IN 1..t.column.count LOOP\n    v_name := t.column(i).description.name;\n    IF t.column(i).description.type = dbms_tf.type_varchar2 THEN  \n      v_str := v_name;\n    ELSIF t.column(i).description.type = dbms_tf.type_number THEN\n      v_str := 'to_char('||v_name||')';\n    ELSIF t.column(i).description.type = dbms_tf.type_date THEN\n      v_str := 'to_char('||v_name||',''YYYYMMDD'')';\n    END IF;\n    v_hdiff := v_hdiff || v_delimiter || v_str;\n  END LOOP;\n  v_hdiff := LTRIM(v_hdiff,'|''#');\n  RETURN 'SELECT  STANDARD_HASH('||v_hdiff||',''MD5'') hash_diff, '||\n         ' e.* FROM '||t.table_name ||' e';\nEND;\n\/\n<\/pre><\/div>\n\n\n
    Listing 6: Implementation for hash diff<\/em><\/strong><\/p>\n\n\n\n
    That’s it! And it works! Even better, in that case the EXPAND_SQL_TEXT procedure is showing us the real SQL statement that runs:<\/p>\n\n\n
    \nSQL> SELECT e.*\nFROM   add_hash_columns (emp) e\nFETCH FIRST 3 ROWS ONLY\n\nHASH_DIFF       EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO\n---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------\n42CB6932B4       7369 SMITH      CLERK           7902 17-DEC-80        800                    20\nAA63299F72       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30\n27332DD16B       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30\n\n3 rows selected.\n\nDECLARE\n  l_clob CLOB;\nBEGIN\n  DBMS_UTILITY.expand_sql_text (\n    input_sql_text  => q'!SELECT e.* FROM   add_hash_columns (emp) e!',\n    output_sql_text => l_clob  );\n  DBMS_OUTPUT.put_line(l_clob);\nEND;\n\/\n\nSELECT "A1"."HASH_DIFF" "HASH_DIFF","A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."JOB" "JOB","A1"."MGR" "MGR","A1"."HIREDATE" "HIREDATE","A1"."SAL" "SAL","A1"."COMM" "COMM","A1"."DEPTNO" "DEPTNO" \nFROM  (\nSELECT "A2"."HASH_DIFF" "HASH_DIFF","A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME","A2"."JOB" "JOB","A2"."MGR" "MGR","A2"."HIREDATE" "HIREDATE","A2"."SAL" "SAL","A2"."COMM" "COMM","A2"."DEPTNO" "DEPTNO" \nFROM  (\nSELECT STANDARD_HASH(TO_CHAR("A3"."EMPNO")||'#'||"A3"."ENAME"||'#'||"A3"."JOB"||\n'#'||TO_CHAR("A3"."MGR")||'#'||TO_CHAR("A3"."HIREDATE",'YYYYMMDD')||\n'#'||TO_CHAR("A3"."SAL")||'#'||TO_CHAR("A3"."COMM")||\n'#'||TO_CHAR("A3"."DEPTNO"),'MD5') "HASH_DIFF",\n"A3"."EMPNO" "EMPNO","A3"."ENAME" "ENAME","A3"."JOB" "JOB","A3"."MGR" "MGR","A3"."HIREDATE" "HIREDATE","A3"."SAL" "SAL","A3"."COMM" "COMM","A3"."DEPTNO" "DEPTNO" \nFROM "ONFTEST"."EMP" "A3") "A2") "A1"\n\n\n<\/pre><\/div>\n\n\n
    Listing 7: Run the SQL with macro<\/em><\/strong><\/p>\n\n\n\n
    Passing column lists<\/h4>\n\n\n\n
    We started with building a hash diff only  and I said it would be easier. Why so? Because we need ALL columns and could simply iterate through them. In case of a hash key we are building a hash out of the business key. Often it is a primary key of the table but not always. The safest way would be to explicitly name the column(s) to be hashed. How do we pass the column list? <\/p>\n\n\n\n
    With a PTF we can use an operator COLUMNS<\/strong> for that. Fortunately, we can also use it with SQL macros! Unfortunately, it is not mentioned in the documentation. Simply pass a list of columns inside this operator. The database will implicitly convert it to the type DBMS_TF.COLUMNS_T<\/strong>, which is a table of DBMS_QUOTED_ID.<\/p>\n\n\n\n
    Now we are ready to extend the implementation (Listing 8). As we iterate through the columns, just check if the current column is member of the columns array (line 21) and build an additional string to hash for the key. <\/p>\n\n\n
    \nCREATE OR REPLACE FUNCTION add_hash_columns(t DBMS_TF.TABLE_T\n                                          , key_cols DBMS_TF.COLUMNS_T) \nRETURN VARCHAR2 SQL_MACRO(TABLE)\nAS\n v_hdiff clob ;\n v_hkey  clob ;\n v_str   varchar2(200);\n v_delimiter varchar2(9):= '||''#''||';\n v_name dbms_id;\nBEGIN\n  FOR I IN 1..t.column.count LOOP\n    v_name := t.column(i).description.name;\n    IF t.column(i).description.type = dbms_tf.type_varchar2 THEN  \n      v_str := v_name;\n    ELSIF t.column(i).description.type = dbms_tf.type_number THEN\n      v_str := 'to_char('||v_name||')';\n    ELSIF t.column(i).description.type = dbms_tf.type_date THEN\n      v_str := 'to_char('||v_name||',''YYYYMMDD'')';\n    END IF;\n    v_hdiff := v_hdiff || v_delimiter || v_str;\n    IF v_name MEMBER OF key_cols THEN\n      v_hkey := v_hkey || v_delimiter || v_str;\n    END IF; \n  END LOOP;\n  v_hdiff := LTRIM(v_hdiff,'|''#');\n  v_hkey := LTRIM(v_hkey,'|''#');\n  RETURN 'SELECT STANDARD_HASH('||v_hkey||',''MD5'') hash_key, '||\n         '       STANDARD_HASH('||v_hdiff||',''MD5'') hash_diff, '||\n         ' e.* FROM '||t.table_name ||' e';\nEND;\n\/\n\nSQL> SELECT e.*\nFROM   add_hash_columns (emp, COLUMNS(empno)) e\nFETCH FIRST 3 ROWS ONLY\n\nHASH_KEY   HASH_DIFF       EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO\n---------- ---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------\n0D7F9017FB 42CB6932B4       7369 SMITH      CLERK           7902 17-DEC-80        800                    20\n7A2B33C672 AA63299F72       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30\nE1E1F667CE 27332DD16B       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30\n\n3 rows selected.\n<\/pre><\/div>\n\n\n
    Listing 8: the final implementation<\/em><\/strong><\/p>\n\n\n\n
    Conclusion<\/h4>\n\n\n\n
    I think, this “marriage” of PTF and SQL macros is a great thing indeed. Look at the Listing 8. We were able to produce the same output as desired in the Listing 1.  We were able to hide the complex logic and provide one generic function to use across the whole project. And we have not compromised the performance in any way! The real SQL statement running is the actually the same as in the Listing 1. So everything happens in the SQL engine with no context switches at all. This use case alone is enough for me to become a fan of SQL macros. But that is not all, stay tuned! <\/p>\n\n\n