0

I have an Oracle 12 database with lots of tables, and I am replicating several of the tables (a subset of rows) into a SQL Server 2016 database. The subset of rows can be established with a WHERE clause on the Oracle side.

I have two web services that can expose anything I want to from that data, one on each side.

Do you have a suggestion for an approach of what I can expose, then compare to find out if the data between the systems matches?

I am currently exposing from one table, which has a few million rows the COUNT(*), which is a no-brainer since it is very efficient. So far, so good.

I'm also exposing the SUM of each of a few NUMBER(18,2) columns and comparing it to the corresponding SUM on the SQL Server side. However, this is problematic, as it has to scan the whole table in SQL Server; it is sometimes blocked, and sometimes might cause other processes to block. I imagine similar problems could occur on the Oracle side too.

Also, the SUM will not tell me if the rows match--it will only tell me that the totals match; if an amount was improperly added to one row and subtracted from another, I wouldn't catch it.

I've pondered whether Oracle's STANDARD_HASH might help me, but it seems cumbersome or error-prone to try to generate the exact same HASH on the SQL Server side, and also this doesn't help with the inefficiency/blocking nature of the call.

So is there any way to have both databases keep track of a hash, checksum, CRC code, or other summary of a column's data, that is efficient to retrieve, and that I can then use to compare to have some idea whether data is the same on both sides? It need not be a perfect solution--for example, comparing SUMs is close but perhaps not quite good enough.

As a first stab, I created an "summary" indexed view, with columns derived from SUMs, on the SQL Server side. This makes querying the view very fast, but incurs additional penalty on every write to the large table underneath. Still, I think it will work, but I'd like to improve on it. Other, better ideas?

6
  • While not perfect, your best bet for change comparison on a row by row basis in SQL Server would be to leverage the HASHBYTES() function and a hashing algorithm not known for collisions. Unfortunately I'm not sure of the equivalent on Oracle's side of the house though.
    – J.D.
    Mar 10 '21 at 20:20
  • That seems error-prone, as I believe it only hashes bytes, and I'd have to be careful that it converted the numbers to a string in the same way, concatenated my strings in the same order, and hashed them in the exact same way on both sides. The first naive attempt failed as oracle gave me decimals without leading zeros (eg. ".5") and SQL Server gave me decimals with a leading zero ("0.5"). I forsee much suffering trying to get them to use the exact same multi-step process. Thanks anyway. Mar 10 '21 at 20:29
  • @PatrickSzalalski I don't follow exactly, a hash is a hash. In SQL Server, the HASHBYTES() function offers a multitude of algorithms to choose from. As long as you choose a collision-free algorithm, "0.5" will always hash as something different than ".5". It's not possible to unhash back to the original value (perhaps that's where your issue occurred?), which isn't necessary for what you're trying to do anyway. I've personally used it as a way to consolidate changes between two instances of the same data in SQL Server. It's indexable as well (e.g. in an index view), which helps performance.
    – J.D.
    Mar 11 '21 at 0:33
  • Ah, re-reading what you wrote, I think I follow. You were saying you want to ensure when you cast your non-string values as strings, they follow the same conversion format in both systems. For me that wasn't a concern since it was all in SQL Server, but I do see your issue with Oracle. I'm sure there is a non-error prone solution anyway, but it would indeed require some careful consideration.
    – J.D.
    Mar 11 '21 at 0:35
  • Yes, that's right. Mar 11 '21 at 2:11
0

The Oracle function for hashing is called

Standard_hash() which supports these methods :- SHA1, SHA256, SHA384, SHA512, and MD5

https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647

In SQL Server you can use Hashbytes() which supports these methods :- MD2, MD4, MD5, SHA, SHA1, or SHA2_256, SHA2_512

https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15

As they both support SHA2_256 or SHA2_512 I would start by setting up a few rows of test data and check that they produce the same result.

a search also throws up the following link http://roelantvos.com/blog/hashing-in-sql-server-and-oracle-for-the-same-output/

EDIT : I reread the comments & saw that you were concerned that standards for conversions of decimals to strings etc were not standard - I think you could fairly easily address this by using the schema & type views to dynamically construct conversion queries using CASE statements etc, & at least it only has to be done on one side.

5
  • This is the brittle solution I hope to avoid...i would never have confidence that I would be 100% perfect in concatenating the string in the exact same format, in the exact row order, then getting it into bytes in the exact same encoding, and doing so over millions of rows. Mar 17 '21 at 19:25
  • You should get 1 hash per row - then you do a full outer join Mar 17 '21 at 20:21
  • You're suggesting I compare millions of rows 1 to 1 rather than one aggregate of all the rows? Mar 17 '21 at 22:11
  • Yes because when you get a difference you will want to know which row is different Mar 23 '21 at 7:52
  • If I were to compare one row at a time, I wouldn't need hashing, as I'd just compare the actual values. Mar 24 '21 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.