Re: is it possible to execute _ALL_ of a query remotely then fetch the results over a db link?
- From: Marc Slemko <identd@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 17 Feb 2005 21:46:33 -0800
On Thu, 10 Feb 2005 02:13:58 -0800, Marc Slemko <identd@xxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
