Comparing Two Tables columnnames,datatype,datalength
Partha, July 18, 2001 - 4:53 pm UTC
Really it is very useful for everybody.
Thanks.
Comparing data in two tables
Sam, November 09, 2001 - 12:39 am UTC
Tom, your example is great and is really usefull especially when working in dev environment with multiple versions of a table.
But it would really be useful , if you show us how to compare the data in two-three tables. Suppose lets say the structure of two tables is similar, then how can we decide if the data in the two tables is different or similar or to what extent it is different.
Is it possible to compare data in two tables when the structure of the tables are different.
The answer to these questions , in my opinion would make this thread more valuable.
November 09, 2001 - 10:21 am UTC
If you want to compare the DATA in the tables -- use the same exact concept -- just for the data.
You of course can only compare apples to apples and oranges to oranges. If the table structure is radically different -- you'll have to determine WHAT and HOW to compare the columns.
Here is a quick example:
ops$tkyte@ORA717DEV.US.ORACLE.COM> create table emp as
2 select ename, empno, e.deptno, d.dname
3 from scott.emp e, scott.dept d
4 where e.deptno = d.deptno
5 /
Table created.
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> REM compare our EMP to their DEPT
ops$tkyte@ORA717DEV.US.ORACLE.COM> REM we obviously cannot compare LOC, we don't have it!
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM>
ops$tkyte@ORA717DEV.US.ORACLE.COM> select *
2 from (
3 select deptno, dname, 'In EMP, not in DEPT'
4 from emp
5 minus
6 select deptno, dname, 'In EMP, not in DEPT'
7 from scott.dept
8 )
9 UNION ALL
10 select *
11 from (
12 select deptno, dname, 'In DEPT, not in EMP'
13 from scott.dept
14 minus
15 select deptno, dname, 'In DEPT, not in EMP'
16 from emp
17 )
18 /
DEPTNO DNAME 'INEMP,NOTINDEPT'
---------- -------------- -------------------
40 OPERATIONS In DEPT, not in EMP
Now -- bear in mind the following:
o EMP has deptno 10 many times, DEPT only has it once -- this sort of difference is NOT reported
o we "lost" deptno 40 when we joined - since its not in EMP, it does show up.
Comparison of Denormalised Tables
Michelle, November 05, 2002 - 5:34 am UTC
We have a denormalised database. The requirement is to freeze data at certain points and then compare the data sets to see whats changed.
This sounds easy enough at parent level as a minus one way states the additions and the minus the other states the deletions.
The problem is however on changes in the child records, because of the joins a change in a child record can result in x rows being created and reverse when the child is being deleted. I have got to the part of identifying these increased or decreased child records but need to find a clever way of identifying why the child record has been deleted or added. Each column has a flag changed column to determine if the column is ammended, added or deleted.
so eg. the below shows that in the new table C2 has been added..... using a minus I can pick out the C2 records but the only change which should be marked is the flag of C2 should read 'New' , as all the other details eg 1 A B C W..W are unchanged...but how can I single out the change was C2?? bearing in mind the change could be any of the columns of A B C W..W and C..C The parent is 1.
Any ideas??
OLD
1 A B C W1 C1
1 A B C W2 C1
1 A B C W3 C1
NEW
1 A B C W1 C1
1 A B C W2 C1
1 A B C W3 C1
1 A B C W1 C2
1 A B C W2 C2
1 A B C W3 C2
Hope I have explained it semi-okay!??
Your help would be appreciated..!
What about performance
David, March 20, 2003 - 9:33 am UTC
Morning, Tom.
Is this the approach you would take if the two tables had a couple of million rows and you needed to compare ALL columns (and there are 70-80 columns)?
I'm trying to update a few million rows, and because UPDATE is relatively slow, I'm going to:
create table as select <do update here> * (see note below)
then
delete from original table
then
insert 'updated' rows into original table.
I know that this is a viable approach when UPDATE simply takes too long, but I want to have reasonable assurances that I have all the rows in my staging table that I'll be deleting from the original table. I thought I'd try the MINUS operator, but on that many rows and that many columns, it seems logical that MINUS might take a good deal of time. If this turns out to be the case, what might your approach be to meet this requirement?
As always, thanks. Your service is invaluable to me.
* Note: If I may sqeeze in a related question, I've researched the problem of the 'do update here' approach (using SQL to do transformations because UPDATE takes so long). There's a good deal of discussion on this topic in your archives. You advocate INSERT or CREATE TABLE AS SELECT rather than array processing or bulk collects, etc. and as usual, my experience confirms your method to be best. But I'd like to have the ability to commit batches into this staging table and as yet I haven't figured out a way to do that from a simple CREATE TABLE AS SELECT statement. Storage becomes a problem in my environment, and I'd like to have 'restartability' if the CREATE TABLE doesn't complete. Your thoughts?
March 20, 2003 - 9:43 am UTC
No, for millions of rows this would take a while.
why wouldn't you just:
create table as select <do the update, to EVERY row, whether it needed it or not>
drop old table
rename new table to old table
anything that "commits" is going to consume even MORE storage??? confused by that request. CTAS in parallel with nologging will just read and write the data ASAP
???
Why I don't think I can drop and rename
David, March 20, 2003 - 10:52 am UTC
It's a production table in a financial application (OFSA). No way I could drop the table. It would take me weeks just to understand the effect of dropping a table in the application. It's taken me some time to convince everyone that create..delete from...insert is the way to go.
So if you can't drop the table, and knowing that MINUS will take time, what alternatives might we have?
March 20, 2003 - 11:18 am UTC
everything will take time. minus is very very brutally effective.
I wondering why you think you can update it if it is a table that is part of a packaged application. Have you verified that is SUPPORTED?
create
truncate
insert /*+ append */
would be better then delete.
Why commit batches
David, March 20, 2003 - 11:03 am UTC
The create table as select sometimes doesn't complete because of tablespace limitations (exceeds maxextents - forgive me, I'm not the DBA.) When it fails, the whole operation rolls back and I might have run for an hour and got nothing accomplished. I have to get the DBA to create a bigger tablespace and I alter the user to use that tablespace, run the create table and alter the user back to the default tablespace.
If I could commit batches, it seems to me, I might get some batches processed before I run out of space. Or since the batches would be smaller, maybe I wouldn't run out of space. At any rate, I could restart and only have to 'update' the rows that didn't get commited in the previous run.
March 20, 2003 - 11:21 am UTC
umm, i would just size it right personally.
orders of magnitude faster.
you should be able to size this by running the CTAS with "and rownum < 10000" added to it, analyze the table to see its size -- and multiply out to the full number of expected rows.
Based on tables data difference how can we populate an insert, update,delete such statements
kumar, March 20, 2003 - 11:43 am UTC
Tom,
I would like to extend this question, and if I have table A and tables B of similar structures ( no matter on same and different databases). Is there any easy way to populate those data difference scripts like inserts, updates, deletes if any. Well this should generate only taking as one the table table A as baseline and generating against table B.
Thanks
Kumar
Audit Trail
A Reader, March 20, 2003 - 1:18 pm UTC
Hi,
Based on an earlier discussion, we've implemented a 'history' functionality against an application that we can't touch (i.e. no triggers etc.) as follows:
each day, take a copy of the tables we want to track -
compare this to the copy you took yesterday using the minus approach described above.
We then have some dynamic sql that goes through the results and figures out the individual changes so we get:
change_date_time change_by change_column change_old_value change_new_value
entries for each update. We don't want deletes or inserts (though we could get them I suspect quite easily)
This runs REALLY quickly (like sub-second for the whole thing) across tables we have with 26000+ rows and is quite neat - the users like it anyway.
The whole thing is pretty much configured from tables so we don't have lots of code to write to get a new table included in the history runs.
If anyone wants any more info - let me know via here.
Regards,
Paul
Why?
A reader, April 01, 2003 - 11:40 am UTC
Hi Tom, your response is very good... but i have a doubt because i don't know what's the difference between not in and minus...
supose a have 2 tables
t1 (code varchar2(5));
t2 (code varchar2(5));
i made this 2 select :
SQL> select code
2 from t1
3 where code not in (select distinct code
4 from t2);
no rows selected
SQL> select code
2 from t1
3 minus
4 select distinct code
5 from t2;
CODE
-----
00001
00002
00003
00005
00006
00007
00009
00010
.
.
.
.
can you explain me why?
Thanks
April 01, 2003 - 7:12 pm UTC
caveat #1
beware the NULL:
ops$tkyte@ORA920> select * from dual where 1 not in ( 2, null );
no rows selected
code in t2 must have some nulls (the distinct is redundant, not necessary btw)... it is not KNOWN if code is not in a set that contains nulls.
caveat #2
beware the sort distinct set operations might incurr...
ops$tkyte@ORA920> select * from ( select * from dual union all select * from dual );
D
-
X
X
ops$tkyte@ORA920> select * from ( select * from dual union all select * from dual )
2 MINUS
3 select 'Y' from dual;
D
-
X
ops$tkyte@ORA920>
a minus b is:
distinct ( a minus b )
get the extra records between two tables
Zo A., April 22, 2003 - 4:07 am UTC
Hi Tom,
Tell me, what is wrong with my syntax here, I would like to get all fields if an extra records exist.
SQLWKS> select count(*) from
2> (select art_code, art_name from agcv_articles
3> minus
4> select art_code, art_name from agc_articles)
5>
COUNT(*)
----------
2255
1 ligne sélectionnée
SQLWKS> select count(*) from
2> (select art_code from agcv_articles
3> minus
4> select art_code from agc_articles)
5>
COUNT(*)
----------
156
1 ligne sélectionnée
My output should look like
111 kjqshds
101 jkljkl
103 jshk
...
102 kjkl
156 records found
April 22, 2003 - 7:47 am UTC
there is nothing wrong with your syntax?
you'll have to tell me more about what you mean by "I would like to get all fields if an
extra records exist."
More Details
Zo A., April 22, 2003 - 9:06 am UTC
What I want is to get the extra records from the view AGCV_ARTICLES which do not exist in the table AGC_ARTICLES. So here I should have 156 extra records. But my problem is if I add more than one field in (select art_code, ART_NAME ...) I get 2255 records instead of 156 records !!! Why ?
SQLWKS> select count(*) from
2> (select art_code, art_name from agcv_articles
3> minus
4> select art_code, art_name from agc_articles)
5>
COUNT(*)
----------
2255
1 ligne sélectionnée
SQLWKS> select count(*) from
2> (select art_code from agcv_articles
3> minus
4> select art_code from agc_articles)
5>
COUNT(*)
----------
156
1 ligne sélectionnée
My output should look like
111 kjqshds
101 jkljkl
103 jshk
...
102 kjkl
Thanks for your quick reply.
Zo
April 22, 2003 - 6:41 pm UTC
Because A MINUS B is really:
sort( distinct(A) ) minus sort(distinct(B))
minus (intersect, union, ... ) inject a distinct step in there.
Also, if the data is:
ART_CODE ART_NAME
--------- ---------
1 x
1 y
ART_CODE ART_NAME
--------- ----------
1 a
1 b
then the two column would return 2 as the count (since (1,x), (1,y) MINUS (1,a), (1,b) is (1,x), (1,y)
and the single column count would return 0 (since (1), (1) minus (1), (1) is the null set)
you probably want
select * from t1 where (art_code) NOT IN ( select art_code from t2)
maybe?
Thanks
Zo A., April 23, 2003 - 1:29 am UTC
Great Tom,
I've changed my sql code into :
select * from agcv_articles where (art_code) NOT IN ( select art_code from agc_articles) as you suggest and it works fine. I thought with MINUS instruction, I can get the same result !!!
Thanks
Zo
comparing same table and insert into the 3rd table with flag
A reader, June 20, 2003 - 8:48 am UTC
tom,
can you please help me with the following..
my requirment goes like this,
i get nightly load on some temp tables and after the load, i populate the table "tb_today". tb_today have a composite primary key.
by next day i will get the next set of load, mostly with changes and very few new inserts.
before my table tb_today gets populated i need to rename the table to "tb_yesterday" and create a new table with the same name tb_today and insert the records from the load.
finally, i will have two tables, tb_today and tb_yesterday. - i wanted to know how to compare the two tables and populate the third table "tb_use_me_everyday" that will have only the values of new inserts and the newly updated records. the new record should be with a flag say 'I' and old record with 'U'
Thank you very much
June 20, 2003 - 5:10 pm UTC
insert into tb_use_me_everyday
select 'U' flag, a.*
from ( select * from tb_today MINUS select * from tb_yesterday );
update tb_use_me_everyday
set flag = 'I' where primary-key-cols not in ( select primary-key-cols from tb_yesterday )
(make sure to be using the CBO and that the primary key cols are NOT NULL for that second query)
that grabs everything in tb_today that isn't in tb_yesterday (updates and inserts) and then marks the records that really didn't exist yesterday as inserted.
that is one way.
Thanks again to Tom !!
A reader, June 22, 2003 - 7:40 am UTC
better approach for handling this ..?
A reader, June 22, 2003 - 7:50 am UTC
from the previous question,
by next day ...i need to rename the table to "tb_yesterday" and create a new table with the same name tb_today and insert the records from the load.
tom, what would be your way of handling this situation, rename the table and create a new table or any other way that would be best for the above
Thanks
June 22, 2003 - 8:22 am UTC
it is "fine" to rename the table
Word of warning.....
A Reader, June 23, 2003 - 7:36 am UTC
Hi,
We are using a solution like this here.... you may hit issues if this thing runs automatically each day and say over a weekend or some days, there was an issue with the load and it isn't spotted, you can 'lose' history that way. Effectively all issues have to be fixed same day or you get your 'yesterday' table overwritten and lose the ability to produce deltas.
Regards,
Paul
thanks to paul for his warning
A reader, June 23, 2003 - 9:37 am UTC
Tom,
what paul says makes sense, though i did remeber this but did not give much importance, other than having a backup is there any other way we can handle it in ease?
thanks Tom!
comparing differenece between two tables - 9i dbms_metadata
Jim Dickson, June 24, 2003 - 11:09 am UTC
If i want to compare ddl of tables in 9i, is there a way to use dbms_metadata?
I can get the 2 ddls but set operator MINUS does not work
(because i believe dbms_metadata's output is XML).
OR is the best way in 9i still to select from user_tab_columns?
June 25, 2003 - 9:26 am UTC
they are clobs, you can use dbms_lob.compare in sql maybe? (but the owner/schema will get in the way)
Is there a way to "diff" two clobs?
A reader, August 07, 2003 - 5:49 pm UTC
Hi Tom
The dbms_lob.compare() just tells whether two lobs
are different or not? Is there any functionality to
do a "diff" and tell what the differences are?
We need to find out the diffs between
different clobs stored in the database.
Thanx!
August 10, 2003 - 11:20 am UTC
there is no "diff" in the lob support packages.
you could....
a) find a java routine out there that does it and load it in as a stored procedure
b) write the lobs out using utl_file and use an OS command run by java to "diff" the files
c) write your own "diff" (non-trivial task)
Comparing the contenst of two tables
Sean, October 25, 2003 - 9:51 pm UTC
Tom,
The script you provided to compare the contents of two tables is very useful for us. Very often I need to compare a lot of reference tables between Dev database and Prod database.
We always use script to patch production database. If someone forgot to write an insert statement in patch script which he did in development work in Dev database, our production database will not get this record during the patching process.
Is it possible to write a stored procedure which takes table name, a temp table name and database link as parameters, compares the contents of two tables (same table, but in different databases) and populates the differences of the table contents to the temp table?
Thanks so much for your help.
Regards,
Sean
October 26, 2003 - 9:37 am UTC
anything is possible. sure, you can do that. you would just be using dynamic sql is all.
Question
PRS, July 03, 2004 - 12:59 pm UTC
Is there any way to compare the two records of the same table using any oracle function or utilities?
I do not want to go field by fiels as I have 100 fields in table plus I want to update only 10 records which are changed in 20 Milion rows table and there is no last modified data column on which I can base any algorithm.
Any idea is appreciated.
Thanks,
PRS
July 03, 2004 - 1:53 pm UTC
you'll have to be a tad more "specific" as to your needs. no idea what you want to do here.
david s, July 23, 2004 - 3:56 pm UTC
in our shop, this showed promise, can it be improved?
set verify off;
var tbl1 VARCHAR2(30);
var tbl2 VARCHAR2(30);
BEGIN
:tbl1 := upper('&1');
:tbl2 := upper('&2');
END;
/
column t1 format a45 heading "Table: &1";
column t2 format a45 heading "Table: &2";
column table_name format a20;
column col_name format a20;
column last_col_name format a20;
column next_col_name format a20;
SELECT
decode(table_name,:tbl1,rpad(col_name,25)||' '||col_data,
decode(col_name,last_col_name,rpad(col_name,25)||' '||last_col_data)) t1,
decode(table_name,:tbl2,rpad(col_name,25)||' '||col_data,
decode(col_name,next_col_name,rpad(col_name,25)||' '||next_col_data)) t2
FROM
(
SELECT
table_name,
column_name col_name,
col_data,
lag(column_name,1,null) OVER (ORDER BY column_name) last_col_name,
lag(col_data,1,null) OVER (ORDER BY column_name) last_col_data,
lead(column_name,1,null) OVER (ORDER BY column_name) next_col_name,
lead(col_data,1,null) OVER (ORDER BY column_name) next_col_data
FROM
(
SELECT
substr(owner,1,10) owner,
table_name,
column_name,
rpad(data_type,8)||
lpad(decode(data_type,
'NUMBER',data_precision||','||data_scale,
data_length),8) col_data
FROM all_tab_columns
WHERE table_name IN (:tbl1,:tbl2) AND
-- owner = 'yourtableowner'
ORDER BY 3,2,1
)
)
GROUP BY
decode(table_name,:tbl1,rpad(col_name,25)||' '||col_data,
decode(col_name,last_col_name,rpad(col_name,25)||' '||last_col_data)),
decode(table_name,:tbl2,rpad(col_name,25)||' '||col_data,
decode(col_name,next_col_name,rpad(col_name,25)||' '||next_col_data))
ORDER BY 1;
david s, July 23, 2004 - 4:03 pm UTC
Sorry, I have observed under additional tests that behavior is not consistent, but would still appreciate your inputs on improvement... 8^)
July 23, 2004 - 5:13 pm UTC
not really sure what I'm looking at or why -- but since it doesn't work? why bother???
how to compare side-by-side two tables
david s, July 24, 2004 - 3:17 pm UTC
Prior inconsistency was from forgetting to put table inputs in alpha order, since query depends on that. This one has worked consistently, I apologize for erroneous earlier posting. No question, just wanted to share our solution with the user community, but would welcome feedback if improvement is needed. This requires your entries of table owners for all_tab_columns; list may be omitted if using user_tab_columns.
set verify off heading off feedback off;
define tbl1 = upper(least('&1','&2'));
define tbl2 = upper(greatest('&1','&2'));
column t1 format a45;
column t2 format a45;
var tbl1 VARCHAR2(30);
var tbl2 VARCHAR2(30);
BEGIN
:tbl1 := &tbl1;
:tbl2 := &tbl2;
END;
/
SELECT
'Table: '||:tbl1 t1,
'Table: '||:tbl2 t2,
chr(13)||chr(10)||rpad('-',45,'-')||' '||rpad('-',45,'-')
FROM dual;
SELECT DISTINCT t1,t2
FROM
(SELECT
decode(table_name,:tbl1,
rpad(column_name,25)||' '||col_data,
decode(column_name,
(lag(column_name,1,null) OVER (ORDER BY column_name)),
rpad(column_name,25)||' '||
(lag(col_data,1,null) OVER (ORDER BY column_name)))) t1,
decode(table_name,:tbl2,
rpad(column_name,25)||' '||col_data,
decode(column_name,
(lead(column_name,1,null) OVER (ORDER BY column_name)),
rpad(column_name,25)||' '||
(lead(col_data,1,null) OVER (ORDER BY column_name)))) t2
FROM
(SELECT
substr(owner,1,10) owner,
table_name,
column_name,
rpad(data_type,8)||
lpad(decode(data_type,
'NUMBER',data_precision||','||data_scale,
data_length),8) col_data
FROM all_tab_columns
WHERE
table_name IN (:tbl1,:tbl2) AND
owner IN (yourownerlist)
ORDER BY 3,2,1))
ORDER BY
nvl(t1,t2);
update or insert
Sean, September 17, 2004 - 3:27 pm UTC
Hi Tom,
I want to migrate data from source table to target table. If data exist in target table (Both table have same primary key), update non primary key column of target table. If data do not exist in target table, insert data into it from source table. Please show how me how to do it in one sql statement.
Thanks so much for your help.
September 17, 2004 - 3:43 pm UTC
a little bit different requirement
Balaji Balasubramanian, September 28, 2005 - 2:38 pm UTC
This is little bit different requirement but very much relevent to this topic. We need to run all the data changes through Quality Control before we save it permenantly to the database, and we are using a staging area to stage the changes for QC people to go through them and save them to the permenant tables. When the QC checks a record, the columns that are changed should appear in different colour.
Our Environment: Oracle EE 920, j2ee, web
Example
we have a table agency
Create table agency(
code number,
name varchar2(100),
adderss_line1 varchar2(100),
address_line2 varchar2(100),
city varchar2(20),
state varchar2(2),
zip varchar2(10),
phone varchar2(14),
contact_person varchar2(50));
insert into agency values(
1001, 'Florida homeless shelter inc', '101 NW 25th ave', null, 'Fort lauderdale', 'FL', '33301', '(954) 333-4444', 'John smith');
And now the agency has got a new contact person, so the agency user logs on to the web app and update the contact_person name, the changes are applied to agency table in the staging area.
Create table agency_staging(
code number,
name varchar2(100),
adderss_line1 varchar2(100),
address_line2 varchar2(100),
city varchar2(20),
state varchar2(2),
zip varchar2(10),
phone varchar2(14),
contact_person varchar2(50),
change_request_id number);
insert into agency_staging values(
1001, 'Florida homeless shelter inc', '101 NW 25th ave', null, 'Fort lauderdale', 'FL', '33301', '(954) 333-4444', 'Sandra jones', '1');
When the QC pulls up this change request, the changed record should be displayed and the changed fields should appear in different color, in this case the contact_person field will appear in different color. QC should also be able to see the existing permenant record to compare.
Is there a quick way of doing this, we need almost like diff function.
Thanks
Bala.
September 28, 2005 - 5:29 pm UTC
by definition the record in staging is "different", so I'll have to assume you really mean "we want to flag the column values that are different"
select a.code,
a.name, b.name, decode( a.name, b.name, 0, 1 ) name_changed,
...
a.zip, b.zip, decode( a.zip, b.zip, 0, 1 ) zip_changed
....
from agency a, agency_staging b
where a.code = b.code;
Thanks Very much
Balaji Balasubramanian, September 28, 2005 - 10:16 pm UTC
A reader, May 23, 2006 - 10:04 am UTC
Please could you help
I have the following:
create table a (x number, a varchar2(3), c number, appl varchar2(3))
insert into a values (1, 'coa', 1, 'BBD');
create table b as select * from a
update b set appl = 'DEB';
commit;
> select * from a;
X A C APP
--------- --- --------- ----
1 coa 1 BBD
> select * from b;
X A C APP
--------- --- --------- ---
1 coa 1 DEB
I want to get the differences (excluding the appl). I am doing this select to get differences between a and b
SELECT x , a, c, appl
FROM
( SELECT spa.*,
1 src1,
to_number(null) src2
FROM a spa
UNION ALL
SELECT spb.*,
to_number(null) src1,
2 src2
FROM b spb
)
GROUP BY x , a, c, appl
HAVING count(src1) <> count(src2);
Unfortunately this select is telling me that there is a difference in the appl.
What I want is when there is a difference other then appl
is to insert this difference accompagnied by their application. For example:
> update b set x=2;
1 row updated.
> commit;
SELECT x , a, c, appl
FROM
( SELECT spa.*,
1 src1,
to_number(null) src2
FROM a spa
UNION ALL
SELECT spb.*,
to_number(null) src1,
2 src2
FROM b spb
)
GROUP BY x , a, c, appl
HAVING count(src1) <> count(src2);
X A C APP
--------- --- --------- ---
1 coa 1 BBD
2 coa 1 DEB
This is fine in this case and I can insert the query result in a log table showing the differences between BBD and DEB.
But when there is no difference (except BBD and DEB) this is not correct for me
Please help
Thanks in advance
May 23, 2006 - 10:09 am UTC
so, stop selecting appl if you don't care about its value
A reader, May 23, 2006 - 10:37 am UTC
No, I need to select the application in order to insert it into a log table showing the differences and from what application they come.
But when there is no difference, I am inserting in the log table the 2 same records with only difference in applications.
Hope you understand me
May 23, 2006 - 3:38 pm UTC
ops$tkyte@ORA10GR2> SELECT x , a, c, min(appl_1) appl_1, min(appl_2) appl_2, count(*) cnt
2 FROM
3 ( SELECT x, a, c, 1 src1, to_number(null) src2, appl appl_1, null appl_2
4 FROM a spa
5 UNION ALL
6 SELECT x, a, c, to_number(null) src1, 2 src2, null appl_1, appl appl_2
7 FROM b spb
8 )
9 GROUP BY x , a, c
10 HAVING count(src1) <> count(src2);
no rows selected
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> update b set c = 3;
1 row updated.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> SELECT x , a, c, min(appl_1) appl_1, min(appl_2) appl_2, count(*) cnt
2 FROM
3 ( SELECT x, a, c, 1 src1, to_number(null) src2, appl appl_1, null appl_2
4 FROM a spa
5 UNION ALL
6 SELECT x, a, c, to_number(null) src1, 2 src2, null appl_1, appl appl_2
7 FROM b spb
8 )
9 GROUP BY x , a, c
10 HAVING count(src1) <> count(src2);
X A C APP APP CNT
---------- --- ---------- --- --- ----------
1 coa 3 DEB 1
1 coa 1 BBD 1
Just make sure cnt = 1, if cnt > 1 - the appl you are getting is the min of the two....
I'm assuming that x,a,c and supposed to be unique in A and B
A reader, May 24, 2006 - 3:37 am UTC
Thanks very much.
It works perfectly
Regards
generic procedure for comparing data which takes column names and the unique keys as input
khushbu, March 28, 2007 - 3:37 am UTC
Hi Tom,
I have almost gone thru all the queries in regard to the comparison of identical tables, but none is really helping me out ..
i want to write a procedure which takes the table name , colnames(which are to be compared) the business keys (or the unique indexes on which the comparison has to be done)
This procedure shud do the comparison on the basis of the Business keys ..
have written an algo for the same
Create or replace procedure data_compare(tabname In varchar2, Colname In Varcha2,Business Keys In VArchar2)
IS
BEGIN
1) Do a record matching analysis
Find the no of records matching based on Business keys
that ll be done by putting a join some wat like this
select a.col2,a.col3 from testa a inner join proda b on a.col2=b.col2 and a.col3=b.col3
2) & 3) the records present in one prod not in test and vice versa can be found out using
select col2,col3,'Test' as TABLENAME from ((select col2,col3 from testa minus (select l.col2,l.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3)))
UNION
select col2,col3,'Prod' as TABLENAME from((select col2,col3 from proda minus (select r.col2,r.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3)))
4)find the records present in both but different
select col2,col3,'Test' as TABLENAME from
((select l.col2,l.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3) minus
(select l.col2,l.col3 from testa l, Proda r where l.col2=r.col2 and l.col3=r.col3))
union
select col2,col3,'Prod' as TABLENAME from
((select r.col2,r.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3) minus
(select r.col2,r.col3 from testa l, Proda r where l.col2=r.col2 and l.col3=r.col3))
after this i want to perform an analysis of the matching records on the non keys
that can be done by a join on all attributes.
select a.col2,a.col3,decode(a.col4,b.col4,'Match','DataMismatch') col4,
decode(a.col5,b.col5,'Match','DataMismatch') col5,
decode(a.col6,b.col6,'Match','DataMismatch') col6,
decode(a.col7,b.col7,'Match','DataMismatch') col7
from testa a inner join
proda b on a.col2=b.col2 and a.col3=b.col3
end of algo
these queries individually helps but writing a generic procedure is not happening ..
kindly help me wit this
March 28, 2007 - 11:52 am UTC
take the query from comparing the contents of two tables:
https://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html ops$tkyte%ORA10GR2> create or replace procedure comp
2 ( p_tname1 in varchar2,
3 p_tname2 in varchar2,
4 p_clist in varchar2,
5 p_cursor in out sys_refcursor
6 )
7 as
8 l_template long := '
9 select $CLIST$, count(src1) CNT1, count(src2) CNT2
10 from
11 ( select $CLIST$, 1 src1, to_number(null) src2
12 from $TNAME1$
13 union all
14 select $CLIST$, to_number(null) src1, 2 src2
15 from $TNAME2$
16 )
17 group by $CLIST$
18 having count(src1) <> count(src2)
19 ';
20 begin
21 l_template :=
22 replace(
23 replace(
24 replace( l_template, '$CLIST$', p_clist ),
25 '$TNAME1$', p_tname1 ),
26 '$TNAME2$', p_tname2 );
27 open p_cursor for l_template;
28 end;
29 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t1 as select * from scott.emp;
Table created.
ops$tkyte%ORA10GR2> create table t2 as select * from scott.emp;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable cursor refcursor
ops$tkyte%ORA10GR2> set autoprint on
ops$tkyte%ORA10GR2> exec comp('T1', 'T2', 'EMPNO, ENAME, HIREDATE, JOB, SAL, COMM', :cursor );
PL/SQL procedure successfully completed.
no rows selected
ops$tkyte%ORA10GR2> update t2 set ename = 'x' where ename = 'KING';
1 row updated.
ops$tkyte%ORA10GR2> exec comp('T1', 'T2', 'EMPNO, ENAME, HIREDATE, JOB, SAL, COMM', :cursor );
PL/SQL procedure successfully completed.
EMPNO ENAME HIREDATE JOB SAL COMM CNT1 CNT2
---------- ---------- --------- --------- ---------- ---------- ---------- ----------
7839 KING 17-NOV-81 PRESIDENT 5000 1 0
7839 x 17-NOV-81 PRESIDENT 5000 0 1
ops$tkyte%ORA10GR2> delete from t1 where ename = 'SMITH';
1 row deleted.
ops$tkyte%ORA10GR2> exec comp('T1', 'T2', 'EMPNO, ENAME, HIREDATE, JOB, SAL, COMM', :cursor );
PL/SQL procedure successfully completed.
EMPNO ENAME HIREDATE JOB SAL COMM CNT1 CNT2
---------- ---------- --------- --------- ---------- ---------- ---------- ----------
7369 SMITH 17-DEC-80 CLERK 800 0 1
7839 KING 17-NOV-81 PRESIDENT 5000 1 0
7839 x 17-NOV-81 PRESIDENT 5000 0 1
Shrikant
Shrikant, May 03, 2008 - 9:58 pm UTC
Hi Tom,
I have 2 databases and want to compare tables data with more than 20 million rows. I have tried both approach of MINUS and UNION -(using count(1) <> 2)- to compare but both are taking very long time.
How can I use BULK COLLECT feature to write a generic procedure to compare all tables in the schema?
Regards
-Shrikant
May 04, 2008 - 10:32 am UTC
... to
compare but both are taking very long time.
....
20,000,000 records over a database link.
think about this for a minute.
and define "a long time" - what are your expectations - you have to ship 20,000,000 records over the wire and then perform the operation. (why do you think YOU doing this slow by slow with bulk collect would be faster, why do you not think this would be SLOWER??)
High expectations from Oracle
Shrikant, May 05, 2008 - 11:58 am UTC
Yes ...you are very correct. I think I am expecting too much from Oracle.
Thanks for the reply Sir.
May 05, 2008 - 2:53 pm UTC
You didn't say how slow is slow... sigh
this has nothing to do with Oracle, everything to do with physics, speed of light and things like that.
Shrikant
Shrikant, May 05, 2008 - 4:24 pm UTC
Tom,
Thanks alot for your reply.
I am doing a migration project and hence data comparision is required.
Out complete load window currently is 3.5 hours. And then when I am executing the data comparision procedure it is taking somewhere close to 3 hours. Hence I said its too slow,
My current logic in dynamic procedure is to do MINUS between 2 databases.
I have time limit of 1 hours as my outbound creations starts after one hour of load.
I was looking for something like
--Dumping recods from each database in array in following way
SELECT *
BULK COLLECT INTO e
FROM table1;
SELECT *
BULK COLLECT INTO e
FROM table1@DBLINK;
and then compare the arrays.
This I can do with single table but confuse on how to fit it in dynamic and also not sure whether it will give me performance benefit.
Regards
Shrikant
May 06, 2008 - 12:55 am UTC
well, since the other database must be read only (since you are comparing - the tables you are 'migrating from' must be static else you would not be able to compare)
why would you not have these tables in the migrated database already - eg: start with them there, restored from a backup or transported from the other source database, precluding the need to compare.
You would really think that fetching millions of rows into arrays and then comparing the arrays (what kind of sort merge are you going to code that will be more efficient than oracle's sort merge - or where you planning on a hash join of your arrays and how would that be more efficient than what Oracle already does)
Thank you so much
Shrikant, May 06, 2008 - 12:26 pm UTC
Tom,
Thank you for suggesting different approach
----
why would you not have these tables in the migrated database already - eg: start with them there, restored from a backup or transported from the other source database, precluding the need to compare.
----
I think this will work.
comparing differenece between two tables
DM, September 23, 2008 - 12:43 pm UTC
Hi Tom
thanks for the wonderful explainations u have given for comparing 2 tables as
1) IN TABLE 1 ,NOT IN TABLE2
2) NOT IN TABLE 1 ,IN TABLE2
I need to know the columns :--
3) IN TABLE1 ,IN TABLE2
Thanks in Advance
September 24, 2008 - 6:29 pm UTC
*just join*
think about it - just join by column name....
compare tables
DM, September 24, 2008 - 12:15 pm UTC
Hi Tom
Thanks for your inputs on comparing two or more tables ...
Can I dynamically comapre two more tables columns??
Quick reply would be appreciated ..
thanks in advance ..
DM
compare tables
DM, September 25, 2008 - 5:14 am UTC
Thanx Tom
Ok then let me put this way ....
I have a TABLE1 like this
SP_ID SP_NM
----------------
1001 AE
1002 AE
1003 AE
2001 BE
2002 BE
TABLE2 like this
SPEC_ID VAR_NM VAR_DTYP VAR_LEN
--------------- ---------------- ----------- ----------
1001 AEDURHR Numeric 2
1001 AEDURMIN Numeric 2
1001 AEMEDSYN Char 100
1002 AEMODIFY Char 200
1002 AEONLDSH Numeric 2
1002 AEMEDSYN Numeric 2
1003 AEONLDSM Numeric 2
1003 AEREL Char 1
if u see it as a table then
Table_1001 will have columns like
AEDURHR Numeric(2)
AEDURMIN Numeric(2)
AEMEDSYN Char(100)
and Table_1002 will have columns like
AEMODIFY Char(200)
AEONLDSH Numeric(2)
AEMEDSYN Numeric(2)
Now I have to compare the values of spec_id:---
Table_1001 with Table_1002
Table_1001 with Table_1003
Table_1002 with Table_1003
NOTE : --We dont have to create tables from table_2....
and so on for 2001 and 2002
I hope I am able to put it properly to understand !
thanx in Advance .....
September 25, 2008 - 3:22 pm UTC
"if u see it as a table then "
who is "U" and why do I care of "U" sees it as a table? I am confused.
I did not get this at all, no. why did 'table 1001' get compared to 1002, 1003 and then 1002 with 1003 - is there some logic you are neglecting to be specific about there - about what gets compared to what? It almost sounds like a non-equi join of TABLE1 A with itself TABLE1 B where a.sp_id < b.sp_id
but more complex - do we compare 1001 with 2001 - if no, why not and why is the data so poorly "modeled" (eg: if 1001 is not to be compared with 2001 - then the 1 and 2 on the leading edge of this four digit number is obviously another FIELD, and should not be a digit in a big number, it should be a separate attribute)
and as I've written perhaps a thousand or more times..
no creates
no inserts
no lookie
oh, and you understand, this is not a simple extension of the original question either - this is entirely, utterly, completely and totally "different", you don't have tables here at all - you have some data and some rules to compare this data.
compare tables
DM, October 29, 2008 - 11:04 am UTC
Hi Tom
For comparing 2 tables structures you are using "all_tab_columns" .
What if one of the table u used in comaprison does not have entry in "all_tab_columns".means that table would be created using the rows of some other table and does not exist now and name of the columns may differ...
Any thoughts on this
Regards
October 29, 2008 - 1:40 pm UTC
that would mean it is a table you do not have access to so therefore, you would not be able to compare them.
so now what?
and who is "U", "U" had nothing to do with this, quite annoying for this "U" person to be stealing the credits here....
If the table has a user accessible column, then the column will appear in all_tab_columns.
You'd need to provide an example if you believe for whatever reason there is cause for concern.
Comparing Two Tables columnnames,datatype,datalength
Thejas, April 21, 2009 - 4:12 am UTC
hi tom,
your example is very useful to compare the structures between two tables, but can we alter the table then and there itself when you compare the tables if so can you explain how.......
Reader, May 01, 2009 - 10:14 am UTC
Tom,
There are 50 tables. I need to count number of records in these 50 tables in prod1 database and prod2 database. There is a dblink to prod2 database from prod1 database. I have to do this everyday.
Example: TableA, TableB
select count(*) from tableA@prod1; - 100
select count(*) from tableA@prod2; - 102
In this case I need to display that, there is difference in number of records tableA.
Can you please suggest a best way to do this?
May 01, 2009 - 10:59 am UTC
generate a script that has
select *
from (select count(*) c1 from t@p1 ) p1,
(select count(*) c2 from t@p2 ) p2
where c1 <> c2;
for each table.
Reader, May 01, 2009 - 11:34 am UTC
Tom,
Thank you for your response. Did you mean SQL plus script?
May 01, 2009 - 11:37 am UTC
sure, whatever scripting environment you want to use.
compare tables
A reader, May 01, 2009 - 5:59 pm UTC
Followup
anand, May 21, 2010 - 12:35 am UTC
Hi Tom
For comparing 2 tables structures you are using "all_tab_columns" .
I am using schema of approx 250 tables.For comparing table structure is very tedious for me.
I want to know which table is having constraints imposed and which is using not null,primary key, foreign key etc.
if you suggest me any solution. if you suggest me about the data difference too it will help me a lot.
Any thoughts on this
Regards
May 24, 2010 - 12:32 pm UTC
there are hundreds of tools out there that do this, sql developer can do this - as can many other tools.
difference between two column
jafery, May 03, 2011 - 3:39 am UTC
Hi Tom
need your valuable answer here. I want to compare two columns of a table. One has old values and other has new values. Lets say Old column has value "A=1;B=2;C=3;" and new column has values "F=2;B=3;". Now i want the result as "A=1;C=3;F=2;B=3;" that is replace the old value (B=2) with the new value B=3 and add any aggregate both column remainig values.
Thanks
Jafery
May 04, 2011 - 12:21 pm UTC
I did not follow this at all.
Replace old data in a column
AskTom Rocks, May 04, 2011 - 12:46 pm UTC
Jafery,
It seems like you want to replace the old value in a column with the new value from a related column in the same table. But first you need to define a logic to compare two columns in the same table (obviously, a table cannot have two columns called "B").
A complete example with a create table statement and data would probably help.
Thanks,
AskTom Rocks.
jafery, May 09, 2011 - 2:10 am UTC
My bad i could not explained properly. below is my example
SQL> create table test (col1 varchar2(20),col2 varchar2(20),col3 varchar2(20));
Table created
SQL> insert into test (col1,col2,col3) values('A=1;B=2;C=3','F=2;B=3','');
1 row inserted
Now i want to populate col3 with values 'A=1;C=3;F=2;B=3'.
Here logic is add all new values of col2 in col1 like (F=2) and replace the value of col1 with col2 if exists like B=3 replace with B=2
I hope i am clear this time. thanks for your time
May 09, 2011 - 8:55 am UTC
this model *STINKS*, I cannot tell you how much I hate seeing data stored in this fashion. Especially given YOUR REQUIREMENT.
Look - you have to process this data- see how horribly hard you've made it???
do you have to do this for ONE row or for MANY rows at a time.
Difference between two columns
jafery, May 11, 2011 - 2:13 am UTC
Hi
I totally agree wid ur comments on this model. But the vendor has made it like this.
Yes we have to process multiple rows (9-12k rows)
Thanks
May 11, 2011 - 10:23 am UTC
why doesn't your vendor provide this capability then??? If it is a requirement of the product you are using - you would think they would.
and wid and ur are NOT words. They make one look like a 12 year old using text messages. Not a professional in the IT industry.
Here is one approach, it could probably be shortened, just sort of banged it out. If you are interested in the table/cast/multiset trick see:
http://asktom.oracle.com/Misc/varying-in-lists.html I suggest you break this query apart to understand it, run it bit by bit - see what is in x_data, y_data, combined and so on - UNDERSTAND it before you just blindly use it :)
ops$tkyte%ORA11GR2> create table t ( x varchar2(30), y varchar2(30), z varchar2(30) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ('A=1;B=2;C=3','F=2;B=3','');
1 row created.
ops$tkyte%ORA11GR2> insert into t values ('X=1;B=20;C=300','Z=2;X=3','');
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> insert into t select t.* from t, (select level l from dual connect by level <= 6000);
12000 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t where rownum <= 5;
X Y Z
------------------------------ ------------------------------ ------------------------------
A=1;B=2;C=3 F=2;B=3
X=1;B=20;C=300 Z=2;X=3
A=1;B=2;C=3 F=2;B=3
X=1;B=20;C=300 Z=2;X=3
A=1;B=2;C=3 F=2;B=3
ops$tkyte%ORA11GR2> set timing on
ops$tkyte%ORA11GR2> merge into t
2 using (
3 with x_data
4 as
5 (
6 select xrid, x, r, x_var, x_val, 1 oc
7 from (
8 select xrid, x, r,
9 substr( x_piece, 1, instr( x_piece, '=')-1 ) x_var,
10 substr( x_piece, instr( x_piece, '=') + 1 ) x_val,
11 new_y
12 from (
13 select rowid xrid, x, to_number( substr(column_value,1,4)) r, substr( column_value, 5) x_piece, new_y
14 from (select x, ';'||x||';' txt, ';'||y new_y from t ) T,
15 table( cast( multiset(
16 select
17 to_char(rownum,'fm0009') || trim( substr (txt,
18 instr (txt, ';', 1, level ) + 1,
19 instr (txt, ';', 1, level+1)
20 - instr (txt, ';', 1, level) -1 ) )
21 as token
22 from dual
23 connect by level <= length(txt)-length(replace(txt,';',''))-1
24 ) as sys.odciVarchar2List ) )
25 )
26 )
27 where instr( new_y, ';'||x_var||'=' ) = 0
28 ),
29 y_data
30 as
31 (
32 select yrid, y, r,
33 substr( y_piece, 1, instr( y_piece, '=')-1 ) y_var,
34 substr( y_piece, instr( y_piece, '=') + 1 ) y_val,
35 2 oc
36 from (
37 select rowid yrid, y, to_number( substr(column_value,1,4)) r, substr( column_value, 5) y_piece
38 from (select y, ';'||y||';' txt from t ) T,
39 table( cast( multiset(
40 select
41 to_char(rownum,'fm0009') || trim( substr (txt,
42 instr (txt, ';', 1, level ) + 1,
43 instr (txt, ';', 1, level+1)
44 - instr (txt, ';', 1, level) -1 ) )
45 as token
46 from dual
47 connect by level <= length(txt)-length(replace(txt,';',''))-1
48 ) as sys.odciVarchar2List ) )
49 )
50 ),
51 combined
52 as
53 (
54 select rid, var||'='||val eav, r, oc, row_number() over (partition by rid order by oc, r) rn
55 from (
56 select xrid rid, x_var var, x_val val, r, oc from x_data
57 union all
58 select yrid, y_var, y_val, r, oc from y_data
59 )
60 order by rid, oc, r
61 )
62 select rid, substr( max( sys_connect_by_path( eav, ';' ) ), 2 ) scbp
63 from combined
64 start with rn = 1
65 connect by prior rn = rn-1 and prior rid = rid
66 group by rid
67 ) new_t
68 on (t.rowid = new_t.rid)
69 when matched then update set z = scbp;
12002 rows merged.
Elapsed: 00:00:01.39
ops$tkyte%ORA11GR2> /
12002 rows merged.
Elapsed: 00:00:00.87
ops$tkyte%ORA11GR2> set timing off
ops$tkyte%ORA11GR2> select * from t where rownum <= 5;
X Y Z
------------------------------ ------------------------------ ------------------------------
A=1;B=2;C=3 F=2;B=3 A=1;C=3;F=2;B=3
X=1;B=20;C=300 Z=2;X=3 B=20;C=300;Z=2;X=3
A=1;B=2;C=3 F=2;B=3 A=1;C=3;F=2;B=3
X=1;B=20;C=300 Z=2;X=3 B=20;C=300;Z=2;X=3
A=1;B=2;C=3 F=2;B=3 A=1;C=3;F=2;B=3
Comparision of 2 tables
VPN, July 04, 2011 - 11:03 am UTC
Hi,
I have 2 tables.
Both my tables contain more than 100 million records.
I want to compare the data between both tables and report the records which are missing in my source and vice versa.
Could you please suggest some info on this.
Regards,
VPN
archive of 2005 is not avalaible
Ajeet, April 15, 2013 - 2:36 pm UTC
Hello Tom
I could not find the above mentioned URL, i can see archieves of oralce magzine till year 2007 only.
https://asktom.oracle.com/magazine-archive.htm the URL above does not work any more.it takes to oracle magzine latest issue and then when i searched in archives it shows the 2007 archives. is there a way to get the script here or some other URL please.
April 22, 2013 - 7:07 pm UTC
Regarding your first query
Federico, March 10, 2015 - 5:31 pm UTC
Hello Tom,
Is there a way to make it work for comparing all tables from 2 schemas?
I got this adaptation of your query, but i'd like you opinion:
(
select 'IN NEW VERSION',table_name, column_name,data_type,data_length
from dba_tab_columns
where owner='SCHEMA NAME'
MINUS
select 'IN NEW VERSION',table_name, column_name,data_type,data_length
from dba_tab_columns@TO_OLD_DB
where owner='SCHEMA NAME'
)
UNION ALL
(
select 'IN OLD VERSION',table_name, column_name,data_type,data_length
from dba_tab_columns@TO_OLD_DB
where owner='SCHEMA NAME'
MINUS
select 'IN OLD VERSION',table_name, column_name,data_type,data_length
from dba_tab_columns
where owner='SCHEMA NAME'
)
I'm trying to compare 2 versions of the same schema to later create a script for migrating the data from the old to the new version, so at first i need to know which tables differ in structure.
This will do the trick or i'm missing something?
Thanks.