HASHROW Function Examples | VantageCloud Lake - HASHROW Function Examples - Teradata Vantage

Example: Calling HASHROW without an Argument

HASHROW without an argument returns 'FFFFFFFF'XB', the maximum hash code in the system.

SELECT HASHROW(); 

Example: Using HASHROW to Return Average Number of Rows Per Hash

The following example returns the average number of rows per row hash, where columns date_field and time_field constitute the primary index of the table eventlog.

SELECT COUNT(*) / COUNT(DISTINCT HASHROW (date_field,time_field)) 
FROM eventlog;

If columns date_field and time_field qualify for a unique index, this example returns the average number of rows with the same hash synonym.

Example: Using HASHROW to Eliminate Synonyms

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.