Oracle equivalent to OPENQUERY (remote SQL execution)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle equivalent to OPENQUERY (remote SQL execution)

  1. #1
    Join Date
    Mar 2006
    Posts
    3

    Oracle equivalent to OPENQUERY (remote SQL execution)

    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):
    Code:
    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!
    Last edited by dtsazza; 03-02-2006 at 06:04 AM.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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:
    This is very easy.
    Create views in server2 and in server3 and use the views in the code.

    Tamil

  3. #3
    Join Date
    Mar 2006
    Posts
    3

    Cool Nice and solved

    Thanks Tamil! I knew there was going to be a nice easy way to do it...

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Quote Originally Posted by dtsazza
    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
    Oracle will not necessarily choose that approach. Have you confirmed that it is doing this?

    btw there is no "AS" for table aliases.

  5. #5
    Join Date
    Mar 2006
    Posts
    3
    No, I haven't confirmed that - the documentation states that queries with remote references as well as local become distributed queries, and that parts of distributed queries are separated into chunks and sent off for remote execution where possible. My main reason for asking was to find out how good the optimiser was in practice - I could run a couple of simple test cases, but I need to know whether it'll be able to separate remote statements out of more complex queries. If it all suddenly blows up and executes sequentially, I probably can't take that performance hit. Asking if there are any common pitfalls (or dos and don'ts) for the optimizer seems prudent given this.

    Views are a good idea for explicitly telling Oracle to execute a certain part of a query remotely. One misgiving I have is that the queries aren't likely to be the same each time, and are created dynamically - how expensive (and indeed possible) would it be to create a view on each remote server from the 'master' server and then execute the SQL, each time? Is there a better way to do this?

    As you may have guessed I haven't had a great deal of experience with Oracle, so I'm just trying to get a feel for the standard ways of handling distributed remote queries.

    Thanks for the help so far!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width