slow queries with DB links

[drkestrel]

I have two DB schemas with same structures, and I would like to compare the contents of one table in the two schemas as follows.

I have created on schema1 (ORACLE_SID=schema1) a Database Link to schema2(ORACLE_SID=schema2) using alias schema2.

I tried running the following queries

SELECT
    code
FROM
    stock
WHERE
    code
    NOT IN
    (SELECT
        code
     FROM
        stock@schema2);

Unforunately, the query seems to take forever to run.

Note, there are more records in the stock table in schema1 than in schema2, so the number of records needed to be pulled back should be less than that if I have the DB link the other way round.

any ways of speeding up the query?

 

[carp]

Using EXISTS might speed it up:

SELECT
code
FROM
stock l
WHERE NOT EXISTS
(SELECT 'x' FROM
stock@schema2 r
WHERE r.code = l.code);

 

[jcote]

My guess is that for every row of stock in schema1, a select is done thru the dblink on stock in schema2. If so, your are executing as many SELECT statement thru the dblink on the second machine as there is rows in the schema1.stock table.

Doing:
select code from schema1.stock
minus
select code from schema2.stock;
would give you what is in schema1 and not in 2.
I am pretty shure that by using this approach, you will get the best performance possible.

 

[rcurva]

Depending on your application, but you can also check snapshots for this matter.