Hi all,
Not sure if this is mentioned earlier in the forum before, but just for my learning ...
Read in one of the blogs posted by Stew Ashton about hash collisions. Interested to explore that part in terms of views from you.. It is quite evident that hash collisions do occur with ora_hash function. (as Stew demonstrated) (myself tested this on 11.2.0.3)
with data as ( select level n, ora_hash(level) ohash
from dual
connect by level <= 200000
)
select count(distinct ohash) number_of_collisions
from data
where ohash in (
select ohash from data
group by ohash
having count(*) > 1
);
Output -- 8
According to documentation :
https://docs.oracle.com/cd/B28359_01/server.111/b28322/man_comp.htm#STREP146
The DBMS_COMPARISON package uses the ORA_HASH function on the specified columns in all the rows in a bucket to compute a hash value for the bucket. If the hash values for two corresponding buckets match, then the contents of the buckets are assumed to match. The ORA_HASH function is an efficient way to compare buckets because row values are not transferred between databases. Instead, only the hash value is transferred.
---------------
Does this mean dbms_comparison is not a better package (and is not advisable to use) as it uses ORA_HASH function to find differences and converge data between two tables? And (as an oracle developer) we should always write customized merge queries with Tom Kyte technique avoiding minus etc manually for synching/converging the data in tables? Any thoughts?
Cheers,
Manik.