title: Nested loop in query over dblink (Oracle 11g)
body: <p>I have a SQL query of the form </p>
  
  <pre><code>SELECT ... FROM A@DB1 a, B@DB1 b, C@DB2 c
  WHERE A.x = B.x and B.y = C.y
  </code></pre>
  
  <p>where the first two tables are dblinks to one database, and the last is on a second database.
  No local tables are accessed.</p>
  
  <p>Explain Plan is showing a nested loop, even though all tables are big and I expect hash join in this situation.  (If all tables were local, I'd expect hash join.) </p>
  
  <p>The "explain plan" looks something like this:</p>
  
  <pre><code>NESTED LOOP 
      HASH JOIN        
          REMOTE     A
          REMOTE     B
      REMOTE         C
  </code></pre>
  
  <p>Further, refreshing stats seemed to have no effect.</p>
  
  <p>Can someone please explain what factors influence how the joins over dblinks get split up and handed out to the remote DBs?  Do any stats play a role in this process and how?</p>
  
  <p>Also, is the 'nested loop' even the real problem, or something else entirely?<br>
  Maybe the real problem is pulling the full contents of A and B locally and joining, rather than joining/filtering remotely.  (Perhaps a <code>DRIVING_SITE</code> hint would fix that?)</p>
  
  <p>Thanks.</p>