Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query that's been in use for years and today it started hanging endlessly. I took the query apart and found the piece causing it to hang is a simple join on a primary key that should run like lightning. If I remove problem table the query runs in less than a second.

Synthetically it looks like this: (The problem table is remote2)

SELECT fields 
FROM local_table1, local_table2, ... (more tables)
     remote_table1@dblink remote1, remote_table2@dblink remote2 
WHERE
      ...clauses to tie the local tables together with each other and remote_table1... 
      remote2.primary_key = remote1.primary_key_of_remote2 
      AND remote2.afield <> 'avoid this value' 

It's acting like it stuck on some kind of DB lock, but neither myself nor the DBA can find it. Is there some kind of special queuing mechanism used by remote database links that is locking this up?

share|improve this question
    
Are you able to retrieve data from remote2 on its own? (check with and without your <> criteria). –  GregHNZ Jul 3 '13 at 6:57
    
Thanks for the ideas. I tried this and it works (much to my suprise): SELECT * FROM remote_table1@dblink iv, remote_table2@dblink il WHERE remote2.primary_key = remote1.primary_key_of_remote2 AND remote2.afield <> 'avoid this value' –  Jeff Jul 3 '13 at 13:45
    
And tried the main query without the <> criteria and it doesn't work. That doesn't make any sense now. –  Jeff Jul 3 '13 at 13:46
    
So... you can get data out of the remote database, but when you try to join two remote tables with local data it chokes? That's weird. Maybe try changing your query to use a join syntax? (i.e FROM R1 JOIN R2 On R1.pk = r2.pk where r2.afield <> 'avoid this') That'll tell the DB engine to separate join logic from the WHERE logic?? (feels hacky, might work) –  GregHNZ Jul 4 '13 at 7:22

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.