From oracle-l-bounce@freelists.org Fri Sep 9 09:03:57 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j89E3vQP014912 for ; Fri, 9 Sep 2005 09:03:57 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j89E3rIP014890 for ; Fri, 9 Sep 2005 09:03:53 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4F2731EB2CA; Fri, 9 Sep 2005 09:03:46 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06685-10; Fri, 9 Sep 2005 09:03:46 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C613D1EB340; Fri, 9 Sep 2005 09:03:45 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: Remote query puzzle X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Date: Fri, 9 Sep 2005 10:07:21 -0400 Message-ID: <3A74FDB223430642A4707A41F72A3BD6643E6C@S-PONYEXPRESS.lechateau.ca> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Remote query puzzle Thread-Index: AcW0G8lvFOZ5KFaZSCCB5s1FLz4dYQBKyvKg From: "david hill" To: , X-archive-position: 25208 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: david.hill@lechateau.ca Precedence: normal Reply-To: david.hill@lechateau.ca X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham version=2.63 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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Dennis Williams Sent: Wednesday, September 07, 2005 10:13 PM To: oracle-l@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@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@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