DWHPro Insights

A Teradata HASHROW Table Difference Screening Test

By Roland Wenzlofsky ·

A Teradata HASHROW Table Difference Screening Test

Have you ever experienced extended waiting times for a table comparison to yield results? Have you ever been compelled to halt and defer quality checks on sizable tables owing to excessive resource utilization during a previous attempt?

Hash comparison can be a compact preliminary screen, but it can miss real differences. It must not be used to certify that tables are equal or to exclude them from required full validation.

HASHROW is not a unique row identifier. In the audit, rows (1,2) and (2,1) had equal hashes while a full-row MINUS detected their difference.

Imagine the following situation:

A copy of a large table has been created. A little later, you wish to check whether a new quality maintenance job has led to corrections to the table content. No rows were added or removed, so a simple row count is not helpful. Assume there is no easy way around a full table content comparison between the before and after data. Imagine further that this is what you have to perform over hundreds of tables.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

A one-direction full-row MINUS finds only rows present on that side and ignores duplicate multiplicity. Run both directions for distinct-set equality; compare grouped full-row counts when duplicate counts matter.

SELECT * FROM DWH.THE_COPY
MINUS
SELECT * FROM DWH.THE_ORIGINAL
;

The following non-executable outline compares hashes only; an empty result does not certify equality:

Alternatively, you can calculate the

SELECT HASHROW( colum1, column2, …, columnN) FROM DWH. THE_COPY
MINUS
SELECT HASHROW( colum1, column2, …, columnN) FROM DWH. THE_ORIGINAL
;

Hash values can reduce comparison width but collisions can conceal unequal rows. Hashing still reads the relevant values; no general claim of avoiding decompression or data access follows.

A historical comparison on a 120 GB input reported resource differences. These measurements were not reproduced, and a faster non-equivalent hash screen is not a substitute for correctness validation.

METHODTotalIOCountAMPCPUTimeMaxCPUAmpNrMinAmpIOMaxAmpIOMaxIOAmpNrSpoolUsage
PLAIN SQL201.755.71670.64182.238.6162.247.648101.786.095.099.904
HASHROW17.184.33333.24919190.144192.24913115.833.006.592

As can be seen clearly, the HASHOW() approach used only a fraction of the PLAIN SQL equivalent resources. Note that we performed this test under ideal conditions of no other parallel activities and with a technical user mighty enough to cope with such a large spool space. We waited over two hours for the first version to finish while we had the second one in half the time.

As can be seen clearly, the HASHOW() approach

Treat an empty HASHROW comparison only as an inconclusive screen. Hash collisions, reversed one-sided differences and differing duplicate counts can all escape detection. Certify equality using the required full-row and multiplicity semantics.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

2 thoughts on “A Teradata HASHROW Table Difference Screening Test”

  1. One flaw, HashRow ignores order, so values A,B,C have the same hash as C,B,A, so inverted data discrepancies will be missed.

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam