Oracle equivalent to OPENQUERY (remote SQL execution)

[dtsazza]

Hi all,

I feel there should be a nice one-line answer to this question, but after a couple of hours of web searching (and a quick search on these forums!) I haven't come across it yet - I'm probably using the wrong terminology...

Anyway, I'm looking for a way to have one Oracle server tell another Oracle server to execute a query remotely - basically equivalent to SQLServer's OPENQUERY. The response should come back in a resultset-type format (terminology letting me down here), e.g. so that I can use it in a FROM clause.

Quick sketch of what I mean (this running on server1):

SELECT foo.bar1,
      	(foo.bar2 + gloop.bar2),
       foo.bar3
FROM   (SELECT Max(subsel.baz1) AS bar1,
               Count(subsel.baz2) AS bar2,
               Sum(subsel.xyzzy) AS bar3
        FROM   (SELECT *
                FROM   table@server2
                ) AS subsel
        ) AS foo,
       (SELECT Max(subsel.baz1) AS bar1,
               Count(subsel.baz2) AS bar2,
               Sum(subsel.xyzzy) AS bar3
        FROM   (SELECT *
                FROM   table@server3
                ) AS subsel
        ) AS gloop
WHERE ...

except so far as I know, with the code as above server1 will retreive all the columns of server2's table, and then do all the calculations itself. For large applications (and greater levels of nesting) this means a lot of data travelling over the wires; what I'd like is for the whole "AS foo" block to be executed atomically on server2, so that the only thing received by server1 is a resultset containing 3 numbers: the max, count and sum of the respective columns on server2. Likewise the "gloop" block should do the same on server3.

From what I can gather about Oracle's distributed database model, if a SQL statement references all its objects at a remote database, it is a 'remote SQL statement' and gets sent to that other database for processing, with the results sent back. My understanding is that this will not occur if there are multiple remote databases referenced - or will the optimiser consider the subselects as separate statements and execute them remotely like this?

Thanks in advance,
Andrzej

P.S., I know the second nested subselect isn't really necessary - but at least it illustrates the multiple nesting that I'll be using in the actual application!