1

I have a SQL query of the form 

SELECT ... FROM A@DB1 a, B@DB1 b, C@DB2 c
WHERE A.x = B.x and B.y = C.y

where the first two tables are dblinks to one database, and the last is on a second database. No local tables are accessed.

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

The "explain plan" looks something like this:

NESTED LOOP 
    HASH JOIN        
        REMOTE     A
        REMOTE     B
    REMOTE         C

Further, refreshing stats seemed to have no effect.

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?

Also, is the 'nested loop' even the real problem, or something else entirely?
Maybe the real problem is pulling the full contents of A and B locally and joining, rather than joining/filtering remotely. (Perhaps a DRIVING_SITE hint would fix that?)

Thanks.

wrschneider
  • 17,913
  • 16
  • 96
  • 176
  • Please provide more info (explain plan, indexes on these tables etc') – Daniel Haviv Dec 15 '11 at 20:01
  • 2
    @wrschneider99, try adding the DRIVING_SITE hint. I'm not sure it will help: a- because it's not always the solution, b- because all tables are in remote DBs but it's worth trying – A.B.Cade Dec 15 '11 at 20:58
  • @A.B.Cade: agree, sometimes I've had success with DRIVING_SITE. I just don't understand why it works or how I can tell whether that's the issue, other than trial and error. – wrschneider Dec 16 '11 at 15:37

1 Answers1

1

This http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_appdev004.htm might help

Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49