0

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!

5

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.

  • thank you very much for all the suggestions you have made. I think this one might be what I need, although I'm not sure if the security will be a problem. will give it a try! – Alex May 28 '12 at 20:59

protected by gnat Feb 18 '18 at 23:52

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.