RE: Remote query puzzle

I had the similar problem with a 9.2 DB

What I found out, is that when selecting from a view on a remote db the criteria doesn't seem to passed to remote db.

If you check for the sql being executed on the remote db you'll probably see the select without the criteria.

The workaround I found was to integrate the view into my select, and select directly from the remote tables instead.

Hope that helps.

David Hill

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dennis Williams Sent: Wednesday, September 07, 2005 10:13 PM To: oracle-l_at_freelists.org
Subject: Remote query puzzle

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

Dennis Williams
--

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

CONFIDENTIALITY NOTICE
This message contains confidential information intended only for the use of the individual or entity named as recipient. Any dissemination, distribution or copying of this communication by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, please immediately notify us and delete your copy. Thank you.

AVIS DE CONFIDENTIALITÉ
Les informations contenues aux présentes sont de nature privilégiée et confidentielle. Elles ne peuvent être utilisées que par la personne ou l'entité dont le nom paraît comme destinataire. Si le lecteur du présent message n'est pas le destinataire prévu, il est par les présentes prié de noter qu'il est strictement interdit de divulguer, de distribuer ou de copier ce message. Si ce message vous a été transmis par mégarde, veuillez nous en aviser immédiatement et supprimer votre copie. Merci.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 09 2005 - 09:03:57 CDT