RE: is it possible to execute _ALL_ of a query remotely then fetc h the results over a db link?
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 10 Feb 2005 10:13:06 -0500
We have a fair amount of distributed and remote SQL. From the description
in this note the SQL statement is a remote SQL and should be sent to the
remote server. It seems to me that is what Oracle normally does for a
statement of the type as described. I do not remember the earlier posts on
the thread so:
What version of Oracle for the local and remote system?
What does the explain plan show as the plan including the OTHER column?
I am not against giving Oracle a little help when the CBO is not doing
things the 'right' way and do not consider the idea of defining the join as
a view on the remote system and selecting from it as inane if I cannot come
up with the correct plan in a reasonable amount of time. What would be inane
is not adopting a workable solution to a problem when you cannot find a
better one.
IMHO -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Marc Slemko
Sent: Thursday, February 10, 2005 5:14 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: is it possible to execute _ALL_ of a query remotely then fetch
the results over a db link?
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
