Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: is it possible to execute _ALL_ of a query remotely then fetch the results over a db link?

Re: is it possible to execute _ALL_ of a query remotely then fetch the results over a db link?

From: Marc Slemko <identd_at_gmail.com>
Date: Thu, 17 Feb 2005 21:46:33 -0800
Message-ID: <9662496505021721461b5c5d80@mail.gmail.com>


On Thu, 10 Feb 2005 02:13:58 -0800, Marc Slemko <identd_at_gmail.com> wrote:
> Ok, I've searched around a bunch. Found a bunch of threads on
> metalink with no real answer other than hacks such as creating a view
> on the remote server and selecting from that, which are an inane
> solution.
>
> My question is this: I have two Oracle 10g boxes, A and B. I create a
> db link from A to B. I have a select query I want to run from A, and
> every table referenced in the query lives on B. How can I force
> Oracle to just send the whole thing to B and then get the results?
>
> The particulars of the query really shouldn't matter... as long as
> every table involved is on B. The DRIVING_SITE hint doesn't do it.
> Using a collocated inline views (as the Oracle docs call it) doesn't
> do it. Using NO_MERGE doesn't seem to do it.
>
> Sure, the select query is slightly hairy. But it is all accessing
> data on B, scanning through a bunch of data, and returning 10 rows or
> so.
>
> I'm just looking for confirmation about if I am missing some easy
> answer here, or if it really has to be hacked together on the
> specifics of each query.
>
> What we are really looking for here is the ability to access a data
> warehouse (ie. complex queries, lots of joins, but few rows returned)
> remotely over a database link to allow execution from something like
> htmldb living on a remote server without having to worry about hand
> tuning each query for the remote execution.

Thanks to everyone for their responses to this, I have just gotten a chance to go through and look at things, here is a summary of some of the suggestions.

  1. Created a pipelined function, procedure, view, etc. on the remote server and pull from that. Yes, that works fine it just isn't practical in this case since it adds a significant amount of complexity to creating the reports, and there are a fairly wide variety of different queries that have to be run; part of the point of using HTMLDB is to make it simple for folks to create the reports. It would be possible I suppose to write some function that takes arbitrary SQL in and executes it then returns results, but I don't think that really makes a lot of sense.
  2. Using push_subq or driving_site hints. I was aware of driving_site and had tried it, without success. I wasn't aware of push_subq (thanks!), but it didn't really have much success either. I realize there are almost certainly ways to optimize specific queries to be executed in a sane manner, but as I stated before that sort complexity just isn't practical for this application.
  3. Install HTMLDB on the dw server directly. This is what we are going to end up doing, but it still isn't desirable for us for the reasons stated in the original post. Issues such as only being able to run a single version of HTMLDB on a server (making testing and deploying upgrades quite difficult), the hundred public synonyms it uses, the higher cost of CPU use on the dw servers compared to on other systems don't make it an overly attractive solution in the general case. But it is what we will work with for now. -- http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 18 2005 - 00:49:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US