Re: Interesting Distributed UNION Question

"Roger Corman" <rogcorpr_at_hotmail.com> wrote in message news:f8285955.0410290518.a3a8eba_at_posting.google.com...
> > Well, if the SOURCE column isn't needed, leave it out and your problem
is
> > solved. A UNION eliminates all redundant rows, so you will only retrive
> > results from either A or B, never from both.
>
>
> Thanks for the reply but you don't understand the question. I know
> that the UNION operation filters redundant rows (the question is not
> related to how UNION functions). BUT, filtering rows implies a trip
> to both Servers which is exactly what I am trying to avoid. I am
> looking for a way to provide optimizer hints within the query so that
> Oracle will only query 1 server and not both, thus the use of the
> SOURCE column.
>
> I have looked at the execution plan which clearly shows a trip to both
> servers when selecting from VIEW where SOURCE = 'A'.
>
> Back to the question... Is it possible to restrict Oracle from making
> a trip to both servers using the SOURCE column of the VIEW?

You need an XOR, which I don't think is implemented in Oracle. An XOR stops looking as soon as it finds the first match. You probably need to do this in PL/SQL. You'd search A first, and if no results, search B. No UNION involved, so is bound to be much faster. Received on Fri Oct 29 2004 - 08:56:27 CDT