CHECKSUM_AGG Equivalent in Oracle

MrEdmundo_52026 0
Viewed 804

I have a query with 

STANDARD_HASH(Column1|Column2|ColumnN, 'MD5') AS HashValue

for each row in a table.

Is it possible to GROUP by ColumnX and produce a Aggregate of the hash values?

I've tried LISTAGG but unfortunately that results in too large a concatenation to hash without error (it's possible I'm not applying this correctly though).

PSEUDO ORACLE presentation of what I'd like to achieve:

SELECT ColumnX, UNKNOWN_AGGREGATE_FUNC(STANDARD_HASH(Column1|Column2|ColumnN, 'MD5'))
FROM TableY
GROUP BY ColumnX

Essentially my reasoning for this, is ideally I'd like to batch up, by ColumnX,the hash values so that I can transport the batch results over the wire for comparison with an external system and therefore not necessarily have to transport every row in the event the batches match.

oracle sql
2 Answers

Oracle 20c supports CHECKSUM analytical/aggregate function:

Use CHECKSUM to detect changes in a table. The order of the rows in the table does not affect the result.

You can use CHECKSUM with DISTINCT, as part of a GROUP BY query, as a window function, or an analytical function.

Pseudocode:

SELECT ColumnX, CHECKSUM(STANDARD_HASH(Column1|Column2|ColumnN, 'MD5'))
FROM TableY
GROUP BY ColumnX;
user_50708790
Related