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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Interesting Distributed UNION Question

Re: Interesting Distributed UNION Question

From: Bricklen <bricklen_at_zyahoo.zcomz>
Date: Fri, 29 Oct 2004 14:16:40 GMT
Message-ID: <chsgd.20839$E93.17889@clgrps12>


Roger Corman wrote:
> I have an interesting situation that I thought justifies a question to
> the group. I have a distributed view that Unions data from two
> different SQL Servers A and B. Both servers have the exact same data
> structure. The view is something like this...
>
> select *, 'A' as SOURCE
> from Table_at_A
>
> UNION
>
> select *, 'B' as SOURCE
> from Table_at_B
>
>
> When I select from the view, I want to filter by either A or B. When
> the database engine executes the select, I would like it to be smart
> enough to know that if Source = 'A' then do not select from 'B'. The
> select would be something like...
>
> select *
> from VIEW
> where SOURCE = 'A'
>
> When looking at the Oracle execute plan, I would hope to see that
> Oracle only selected from Server A and did not waste time querying
> Server B. If this isn't possible, I will be forced to create two
> distinct views, one for each server. Also note that I can't generate
> the view or select statement dynamically since it will be called by an
> application that can only pass WHERE clause filters, such as SOURCE.
>
> Any thoughts on whether this is possible? Any alternatives? Thanks
> for any feedback.
>
> RC

Without having tried it (and being reasonably early in the morning), would a CASE statement work?
eg.
select case when :source='A' then (select * from table_at_A) else (select * from table_at_B) end from dual;

or something along those lines. No idea if it would work with a distributed query, though. Received on Fri Oct 29 2004 - 09:16:40 CDT

Original text of this message

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