Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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!

share|improve this question
    
If you have access to unix tools, look into diff on the flat files instead of Python. –  user1249 May 28 '12 at 20:25
    
There are tools that can probably do what you want. Oracle's (Free!) SQL Developer can do it, and there are others: blogs.devart.com/dbforge/… –  FrustratedWithFormsDesigner May 28 '12 at 20:33
    
    
I just discovered in Oracle 11g that there's a dbms_comparison utility package: docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_comparison.htm –  FrustratedWithFormsDesigner May 28 '12 at 20:42
    
If you still go with a custom PL/SQL solution, you can use database links to let code in once database see objects in a different database. docs.oracle.com/cd/B12037_01/server.101/b10759/… You might have to tweak the permissions a bit in the remote database, but it should still work. –  FrustratedWithFormsDesigner May 28 '12 at 20:44
add comment

1 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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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