how to compare two large sets of data using PL/SQL?

Question

I have two different tables in two separate databases with around 1.5 million records each. (BTW: im working on oracle databases)

The objective is to check if every record present in table A is also present in table B. Since the tables exist in different databases, I think I cannot calculate the "difference" between the two of them, so what im currently doing is selecting the column I need from each database and storing all the rows on separate text files. Then, I load the content of one of the files on a dictionary (using python) and then iterate the other file checking if every line is present on the dictionary.

I would like to make this more simple, perhaps directly on a stored procedure in the database. My main concerns are:

1. I don't know if PL/SQL associative arrays are as efficient as the dictionary (im assuming python dictionaries have constant lookup time)
2. The fact that tables exist on different databases makes me think that it would be impossible to "reach" the contents of table B if the stored procedure is on the database of table A, or viceversa.

Thanks in advance for any suggestion!

Answer 1

If there are no security restrictions that prevent that, probably the easiest way is to create a database link, so those two databases see each other; then use the minus clause to find what is in one table but not the other.

CREATE DATABASE LINK otherdb
  CONNECT TO otheruser IDENTIFIED BY otherpwd USING 'otherdb.wherever';

SELECT mycolumn FROM mytable MINUS SELECT mycolumn FROM mytable@otherdb;

SELECT mycolumn FROM mytable@otherdb MINUS SELECT mycolumn FROM mytable;

Copying the table from one machine to the other (using 'CREATE TABLE xxx AS SELECT * FROM mytable@otherdb') and using that for the comparison might be faster, though.