Table relationship

[icebox27]

Good Day everyone!

I need urgent help on this.

I have to tables with both having 8 primary keys. Both having more than 25,000 records above. I need a script that will determine which record in one of those two tables doesnt exist in the other table(meaning has no related data using the 8 primary keys). The established relationship using the 8 primary keys is always 1:1.

Need your assistance, everyone.

Regards

[Dave44]

Good Day everyone!

I need urgent help on this.

I have to tables with both having 8 primary keys. Both having more than 25,000 records above. I need a script that will determine which record in one of those two tables doesnt exist in the other table(meaning has no related data using the 8 primary keys). The established relationship using the 8 primary keys is always 1:1.

Need your assistance, everyone.

Regards

First... 8 fields yikes!! you should consider using a surrogate key (sequence field) as the primary key and just use a unique index on the 8 fields making up the primary key.

as to your question you could do a minus between the two tables, remember to do it both ways because you want to know which rows in either table doesnt have a like row in the other. Minus only finds it one way, so for example

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
[144]dave@ORADB> create table t1 (col1  number);
 
Table created.
 
Elapsed: 00:00:00.42
[144]dave@ORADB> create table t2 (col1  number);
 
Table created.
 
Elapsed: 00:00:00.04
[144]dave@ORADB> insert into t1 values (1);
 
1 row created.
 
Elapsed: 00:00:00.00
[144]dave@ORADB> insert into t2 values (2);
 
1 row created.
 
Elapsed: 00:00:00.00
[144]dave@ORADB> select col1 from t1 minus select col1 from t2;
 
      COL1
----------
         1
 
Elapsed: 00:00:00.01
[144]dave@ORADB> select col1 from t2 minus select col1 from t1;
 
      COL1
----------
         2
 
Elapsed: 00:00:00.00
[144]dave@ORADB> SELECT col1, 'T1' tab FROM t1 minus SELECT col1, NULL FROM t2
  2  UNION SELECT col1, 'T2' tab FROM t2 minus SELECT col1, NULL FROM t1;
 
      COL1 TA
---------- --
         1 T1
         2 T2
Elapsed: 00:00:00.00
 

with 25K rows in each table this should be fine, but i wouldnt use it on tables with many millions, it could get really slow.