Re: ideas to use ora hash
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
- Date: Wed, 22 Mar 2006 09:36:56 -0800
On 3/22/06, Dennis Williams <oracledba.williams@xxxxxxxxx> 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;
/
Other related posts:
