Re: Remote query puzzle

Dennis Williams wrote:
> I have a query that incorporates a view on a remote database. A
> straight query against the remote view works fine.
>
> select count(*) from remote_view_at_remote_db where value = 'ABC';
>
> However, if I create a small local table and use it to select against
> the remote view,
>
> select count(*) from remote_view_at_remote_db where value in (select col1
> from small_table);
>
> Then the remote view reacts differently and commits a full table scan
> against one of the large tables included in the remote view. I have
> tried the DRIVING_SITE hint and that takes effect, but does not change
> the FTS. I tried to use a second hint in addition to the DRIVING_SITE
> hint, an INDEX hint on a table within the remote view. However, this
> did not change the query behavior.
>
> Any ideas welcome.
> Oracle 8.1.7.4
> Rule-based optimization

Not sure these hints mean anything to the RBO...

Are the local and remote DB's at the same version? Are both open read/write?

To get any further, you will probably need to post your hint syntax and query plan explanations, if not a reproducible test case. But I don't have an 8i database at hand, so I won't be able to reproduce that part in any case.

-- 
Mark Bole
http://www.bincomputing.com



--
http://www.freelists.org/webpage/oracle-l