Re: Oracle table compare

[Edward Lock <ejlock@MSN.COM>]

Depending on the Oracle version that you're using, you can use a "full-outer 
join" clause to pick up non-matching rows in both tables.  I know that it's 
available in 9i, and perhaps 8i.


----- Original Message -----
From: Harish_Venkiteswaran
Sent: Wednesday, March 27, 2002 6:40 AM
To: SQR-USERS@list.iex.net
Subject: Oracle table compare

Hi

We want to a compare single table in two databases each having min 15
million rows.The tables used are A and B .

Criteria
The  rows exists in A but not in B and the row existing in B and not in A.
For that we are using  the following  query

(I)     SELECT EMPLID FROM A@Adb
        MINUS SELECT EMPLID FROM B@Bdb
        UNION
        SELECT EMPLID FROM B@Bdb
        MINUS SELECT EMPLID FROM A@Adb

If the key values are the same in both tables(ie the row is present in the
two databases) then the above query will return 0 rows.

(II)Also we need to output rows in which keys are same but the data is
different.We are using an equi join to do this.

        We are having performance problems when we use the  (I) query.Is
there any other way of doing I and II?.


Thanks & Regards
Harish

**************************************************************************
This email (including any attachments) is intended for the sole use of the
intended recipient/s and may contain material that is CONFIDENTIAL AND
PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or
distribution or forwarding of any or all of the contents in this message is
STRICTLY PROHIBITED. If you are not the intended recipient, please contact
the sender by email and delete all copies; your cooperation in this regard
is appreciated.
**************************************************************************