Row comparision for 2 identical tables

Hi Guys,

Been asked to look at row comparision in 2 identical tables, and report on rows with differenct data.

For example, I have 2 tables of identical structure - table1 and table2.

Create table TABLE1 (or TABLE2)
( cust_num number(4),
favourite_colour varchar2(10),
favourite_car varchar2(10))

The primary key on both is cust_num.

I need to output the cust_num and the values on both tables, where the values do not match for the primary key. The code should only output the columns where a difference exists.

For example -

CUST_NUM TABLE1.FAVOURITE_COLOUR TABLE2.FAVOURITE_COLOUR
-------- ----------------------- -----------------------

123 RED BLUE




Can anyone give me starter (or complete solution, if you're feeling generous). I'm scheduled to look at this tomorrow, and would appreciate any early input.

(ps - this is not my homework!!)

Thanks.

Are you looking to find rows where:

A). the primary key is different, but all the rest of the data is the same (so a row from table1 is related to a row in table2).

Code:

---

15:01:53 SQL> create table temp_table1 (cust_num number(4),
15:03:03  2  favourite_colour varchar2(10),
15:03:03  3  favourite_car varchar2(10),
15:03:03  4  constraint temp_table1_pk
15:03:03  5  primary key (cust_num));

Table created.

15:03:40 SQL> create table temp_table2 (cust_num number(4),
15:03:50  2  favourite_colour varchar2(10),
15:03:50  3  favourite_car varchar2(10),
15:03:50  4  constraint temp_table2_pk
15:03:50  5  primary key (cust_num));

Table created.

15:20:06 SQL> select * from temp_table1;

  CUST_NUM FAVOURITE_ FAVOURITE_
---------- ---------- ----------
        1 red        porsche
        2 blue      ferrari
        4 orange    Skoda

15:20:19 SQL> select * from temp_table2;

  CUST_NUM FAVOURITE_ FAVOURITE_
---------- ---------- ----------
        1 red        porsche
        3 blue      ferrari
        5 orange    Beetle

15:20:39 SQL> select a.*, b.*
15:20:57  2  from (select * from temp_table1 where cust_num not in(
15:20:57  3  select cust_num from temp_table2)) a,
15:20:57  4      (select * from temp_table2 where cust_num not in(
15:20:57  5  select cust_num from temp_table1)) b
15:20:57  6  where a.favourite_colour=b.favourite_colour
15:20:57  7  and a.favourite_car=b.favourite_car;

  CUST_NUM FAVOURITE_ FAVOURITE_  CUST_NUM FAVOURITE_ FAVOURITE_
---------- ---------- ---------- ---------- ---------- ----------
        2 blue      ferrari            3 blue      ferrari

15:20:58 SQL>

---

or are you looking for:

B). Any row that that is in one table, but not in the other and the rows bear no relation to each other?...

(select cust_num, favourite_colour from table1
minus
select cust_num, favourite_colour from table2)
union all
(select cust_num, favourite_colour from table2
minus
select cust_num, favourite_colour from table1);

???

=======
(select cust_num, favourite_colour from table1
minus
select cust_num, favourite_colour from table2)
union all
(select cust_num, favourite_colour from table2
minus
select cust_num, favourite_colour from table1);

========
The above SQL is to run slow.
There will 2 full table scans on table1 and another 2 full table scans on table2. Isn't it bad?

This above SQL was modified by Tom and another brilliant Italian (I forgot his name). Their version is given below:

PHP Code:

---

select c1, c2, c3, count(src1), count(src2)
from
(
    select a.*, 1 SRC1, to_number(null) SRC2 
    from   a
    union  all
    select b.* , to_number(null) SRC1, 2 SRC2
    from   b
)
group by c1, c2, c3
having count(src1) <> count(src2)
/ 


---

Tamil

Quote:

---

Originally posted by tamilselvan
This above SQL was modified by Tom and another brilliant Italian (I forgot his name). Their version is given below:

---

http://asktom.oracle.com/~tkyte/compare.html

Thanks for the link to Tom's page. I'm still working through all that.

What I need is......

Assuming both tables primary keys are identical and same rowcount in tables, for the same primary key match, I need to output if col1 in table1 is different to col1 in table2, OR if col2 in table1 is different to col2 in table2, OR if col3.......etc.

Output would look something like -

For Cust_num 12345, table1.col3 has the value RED, but table2.col3 has the value BLUE

Too obvious to be what you are looking for Horace, but it seems to match your requirements in the last post...

Code:

---

11:23:40 SQL> select * from temp_table1;

  CUST_NUM FAVOURITE_ FAVOURITE_                               
---------- ---------- ----------                               
        1 red        porsche                                   
        2 blue      ferrari                                   
        4 orange    Skoda                                     
        6 purple    mini                                     

11:23:47 SQL> select * from temp_table2;

  CUST_NUM FAVOURITE_ FAVOURITE_                               
---------- ---------- ----------                               
        1 red        porsche                                   
        3 blue      ferrari                                   
        5 orange    Beetle                                   
        6 red        mini                                     

11:23:52 SQL> select a.*, b.*
11:23:59  2  from temp_table1 a,
11:23:59  3      temp_table2 b
11:23:59  4  where a.cust_num = b.cust_num
11:23:59  5  and (a.favourite_colour<>b.favourite_colour
11:23:59  6  or a.favourite_car<>b.favourite_car);

  CUST_NUM FAVOURITE_ FAVOURITE_  CUST_NUM FAVOURITE_ FAVOURITE_
---------- ---------- ---------- ---------- ---------- ----------
        6 purple    mini                6 red        mini     

11:24:00 SQL> spool off

---

What would you output if both columns were different? Two rows or perhaps an essay?

That's the problem padders. I've used a small table as an example. The actual tables have 70 columns each. The developers are sure that the tables should match identically, but we (the dba team) would like to prove, rather than assume.

We'd also like a readable output. With the assumption that the tables are 'near' identical, the output shouldn't be an essay!!

OK, no essay. That bit was a joke.

Now how about you tell me what would you output if both (or n) columns were different? Two (or n) rows ?

To check if they are equal, isn't there a function that calculates a hash value of the whole row? (never used it)

For the output, how about:
REPLACE(tab1.col1, tab2.col1)||REPLACE(tab2.col1, tab1.col1) etc etc
gives you NULL if identical and something to work with if they are not (I'm assuming a quick & dirty is OK).

Quote:

---

function that calculates a hash value of the whole row

---

Can't find any reference to that on metalink. Sounds useful. Anybody know what this is?

Later I got as far as finding this: http://www.oracle-base.com/articles/...gMethods9i.php
look under "Checksum" - however

Quote:

---

increases the amount of code necessary since the CHECKSUM function cannot be used as part of the SQL statement.

---

. . but I couldn't track down "owa_opt_lock.checksum" . . . good hunting!

owa_opt_lock.checksum function :
===================================

Syntax
owa_opt_lock.checksum(p_buff in varchar2) return number;
owa_opt_lock.checksum(
p_owner in varchar2
p_tname in varchar2
p_rowid in rowid) return number;

Purpose :
-------------
This function returns a checksum value for a specified string, or for a row in a table. For a row in a table, the function calculates the checksum value based on the values of the columns in the row. This function comes in two versions.

The first version returns a checksum based on the specified string. This is a "pure" 32-bit checksum executed by the database and based on the Internet 1 protocol.

The second version returns a checksum based on the values of a row in a table. This is a "impure" 32-bit checksum based on the Internet 1 protocol.

Parameters :
--------------
p_buff - the string for which you want to calculate the checksum.

p_owner - the owner of the table.

p_tname - the table name.

p_rowid - the row in p_tname for which you want to calculate the checksum value. You can use the owa_opt_lock.get_rowid function to convert vcArray values to proper rowids.

Return Value :
----------------
A checksum value.

Had a go at dinnertime yesterday to compare two tables. This is the code so far, it currently relies on primary key match between
the two tables (col1), and needs some work still. It uses owa_opt_lock.checksum. Not considered the output of erroneous data as yet.

Yes, some of the variables could be amalgamated into single select statements, but for clarity, I've used variables
for each stage of the process. Change col4 in any table to cause a mismatch.

It's a start..........

Code:

---

>> desc horace1
 Name          Type
 ------  -----------
 COL1      NUMBER(2)
 COL2      NUMBER(2)
 COL3      NUMBER(2)
 COL4      VARCHAR2(2)

>> desc horace2
 Name          Type
 ------  -----------
 COL1      NUMBER(2)
 COL2      NUMBER(2)
 COL3      NUMBER(2)
 COL4      VARCHAR2(2)


>> select * from horace1;

    COL1      COL2      COL3 COL4
--------- --------- --------- --
        1        2        3 AB
        2        2        3 AB
        3        2        3 AB

>> select * from horace2;

    COL1      COL2      COL3 COL4
--------- --------- --------- --
        1        2        3 AB
        2        2        3 AB
        3        2        3 AB


DECLARE

    v_rowid1  rowid; /* stores rowid from first table */
    v_rowid2  rowid; /* stores rowid from second table */
   
    checksum1 number; /* stores checksum for rowid from first table */
    checksum2 number; /* stores checksum for rowid from second table */


    cursor CUR_TABLE1 is select H1.rowid,H1.col1      /* grabs rowid and primary key(col1) from first table */
                        from horace1 H1,horace2 H2  /* where primary key matches second table            */
                        where H1.col1 = H2.col1; 

    different_content  EXCEPTION;  /* exception to handle when tables have different contents */

   
     
BEGIN

  FOR REC_TABLE1 in CUR_TABLE1

    LOOP

    v_rowid1 := REC_TABLE1.rowid;  /* store rowid from first table */

    select rowid into v_rowid2
    from HORACE2
    where col1 = REC_TABLE1.col1;  /* store rowid from second table where primary key matches first table */

    checksum1 := owa_opt_lock.checksum('WAREHOUSE_PROD','HORACE1', v_rowid1); /* calculate checksum for row in table 1 */
    checksum2 := owa_opt_lock.checksum('WAREHOUSE_PROD','HORACE2', v_rowid2); /* calculate checksum for row in table 2 */

 --  DBMS_OUTPUT.PUT_LINE(checksum1); /* test and debug */
 --  DBMS_OUTPUT.PUT_LINE(checksum2); /* test and debug */

      IF checksum1 = checksum2 /* compare checksums */

      THEN

        null;  /* if checksums match, do nothing */

      ELSE

        raise DIFFERENT_CONTENT;  /* if checksums do not match, raise exception */

      END IF;

    END LOOP;

DBMS_OUTPUT.PUT_LINE('Tables match');

  EXCEPTION

    WHEN DIFFERENT_CONTENT
    THEN    DBMS_OUTPUT.PUT_LINE('Tables do not match');
   
    WHEN OTHERS
    THEN DBMS_OUTPUT.PUT_LINE('Something else is wrong');

END;
/

---