Re: Interesting Distributed UNION Question

"Roger Corman" <rogcorpr_at_hotmail.com> wrote in message news:f8285955.0410271137.5abd77d3_at_posting.google.com...
> 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

This smacks of homework. Apologies if it isn't, but here's a hint if it is (or question to be answered if it isn't)...

Why do you need the SOURCE column? Received on Wed Oct 27 2004 - 15:05:09 CDT