16.20 - Example - Advanced SQL Engine - Teradata Database

The following example evaluates the efficiency of changing the decimal format of a numeric field to eliminate synonyms.

Assume that column_1 and column_2 are declared as DECIMAL(2,2).

You can determine the effect of reformatting the columns to DECIMAL(8,6) and DECIMAL(8,4) on hash collisions by submitting these two queries.

   SELECT COUNT (DISTINCT column_1(DECIMAL(8,6)) ||
   column_2(DECIMAL(8,4))
   FROM T;
   
   SELECT COUNT (DISTINCT HASHROW (column_1(DECIMAL(8,6)), 
   column_2 (DECIMAL(8,4)))
   FROM T;

If the result of the second query is significantly less than the result of the first query, there are a significant number of hash collisions. That is, the closer the second result is to the first value indicates elimination of more hash synonyms.