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.
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 bookA 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.
| METHOD | TotalIOCount | AMPCPUTime | MaxCPUAmpNr | MinAmpIO | MaxAmpIO | MaxIOAmpNr | SpoolUsage |
| PLAIN SQL | 201.755.716 | 70.641 | 8 | 2.238.616 | 2.247.648 | 10 | 1.786.095.099.904 |
| HASHROW | 17.184.333 | 33.249 | 19 | 190.144 | 192.249 | 13 | 115.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.
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
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.
Thanks for pointing this out.