Tuesday, June 28, 2011

Check summing a table row

Oracle has a built-in package for optimistic locking called OWA_OPT_LOCK. This can be used to generate a checksum for any row like this:

select owa_opt_lock.checksum('SCOTT','EMP',ROWID)
from empwhere empno = 123;

This nugget fell into the bucket of "Things I discovered, but couldn't actually find when I wanted to use it". Then it cropped up as a Stackoverflow answer, so I'm putting it in here so I can find it.
Apparently it is a very simple checksum involving summing up the binary values and inverting it. It isn't a sophisticated hash designed to give low chances of collisions. The code isn't wrapped, so you can have a peek. It seems to do a simple concatenation of the values (implicitly converted to strings), so definitely isn't bulletproof or super-secure. Honestly, it wouldn't have done the job I was attempting when I couldn't find it.

If you want a simple sanity check that a small table on one database has the same contents as it does on another, then it will generally work okay. But for each call (and therefore each row, if you want it for an entire table's contents), it will do queries against all_tab_columns. So it won't be quick.

Anything big, and I'd hack up my own select based on

select 'sum(ora_hash('||column_name||')),'
from all_tab_columns 
where table_name = '...'

which would also say WHICH columns had differences.


Damir Vadas said...

Could you explain a little bit your way of calculation and finding difference columns?

SydOracle said...

Hope the subsequent post explains things