Solved

ORACLE SLQ - Comparing records in 2 tables

Posted on 2008-06-13
6
960 Views
Last Modified: 2013-12-07
Table 2 retrieves data from existing fields in Table 1.  The system also creates additional fields in Table 2.  Not all fields are transfered from Table 1 to Table 2.  These tables may have millions of records so efficentcy is important.

I need to compare that the records are accurately populated in Table2 from Table1.  

              TABLE 1                                      TABLE 2
           Field1                                               Field1
           Field2                                               Field2
           Field3                                               Field3
           Field4                                            
           Field5                    
                                                                  Field6
                                                                  Field7
0
Comment
Question by:SharonInGeorgia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 125 total points
ID: 21781371

SELECT * FROM TABLE1 A
 WHERE NOT EXISTS (
    SELECT '?' FROM TABLE2 B
     WHERE A.FIELDX = B.FIELDX );

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 125 total points
ID: 21781470
The attached query compares every record in table1 OR table2 with every record in table1 AND table2. A perfect match will return no rows.

compare.txt
0
 

Author Comment

by:SharonInGeorgia
ID: 21782331
I understand that using UNION can be slow when dealing with massive amount of records.  What are your thoughts?
0
 
LVL 32

Expert Comment

by:awking00
ID: 21788752
In this case, I would probably use UNION ALL instead of UNION, which should avoid an extra sort, although the results are the same.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question