Re: ideas to use ora hash

On 3/22/06, Dennis Williams <oracledba.williams_at_gmail.com> wrote:
>
> I have a task to compare two large tables on different databases. All
> columns need to be compared for changes. Would a hash value reduce the
> amount of data to compare?
>
>

Here's a prototype.
This code is not optimized. :)

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


============================================================

drop table t1 cascade constraints;
drop table t2 cascade constraints;

create table t1
as
select *
from dba_tables
/

create table t2
as
select *
from t1
/

-- modify some data in the second table
update t2
set tablespace_name = 'NEWTBS'
where table_name like '%Z%'
/

commit;

declare
   v_col_str varchar2(32767) := '';
   n_hash_1 number;
   n_hash_2 number;
begin
   for crec in (
      select column_name
      from user_tab_columns
      where table_name = 'T1'
      order by column_name
   ) loop
      v_col_str := v_col_str || crec.column_name || '||';
   end loop;
   v_col_str := substr(v_col_str,1,length(v_col_str)-2);


   -- get table names and owners
   for trec in (select owner,table_name from t1 ) --where rownum < 10)
   loop
      -- get first hash value
      execute immediate 'select dbms_utility.get_hash_value(' || v_col_str
|| ',1048576,1073741824)
         from t1 where owner = :1 and table_name = :2 '
         into n_hash_1 using trec.owner,trec.table_name;

      -- get second hash value for matching key in table 2
      execute immediate 'select dbms_utility.get_hash_value(' || v_col_str
|| ',1048576,1073741824)
         from t2 where owner = :1 and table_name = :2 '
         into n_hash_2 using trec.owner,trec.table_name;


      if n_hash_1 != n_hash_2 then
         dbms_output.put_line('Data does not match!!');
         dbms_output.put_line('TABLE: ' || trec.table_name );
         dbms_output.put_line(rpad('=',40));
      end if;

   end loop;


end;
/

--
http://www.freelists.org/webpage/oracle-l