From oracle-l-bounce@freelists.org Fri Sep 9 10:31:30 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j89FVUsC029686 for ; Fri, 9 Sep 2005 10:31:30 -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 j89FTFIP029142 for ; Fri, 9 Sep 2005 10:29:21 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 648A71EB206; Fri, 9 Sep 2005 10:29:08 -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 23111-10; Fri, 9 Sep 2005 10:29:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DF2D71EB1CE; Fri, 9 Sep 2005 10:29:07 -0500 (EST) X-SEF-Processed: 5_0_0_713__2005_09_09_11_27_12 X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: Forcing SQL query to use 2 index hints Date: Fri, 9 Sep 2005 11:27:11 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Forcing SQL query to use 2 index hints Thread-Index: AcW1Ub1Gg9tkAqqkQe66GfV00vgj0gAANrcg From: "Stauffer, Robert G" To: , X-OriginalArrivalTime: 09 Sep 2005 15:27:12.0636 (UTC) FILETIME=[F37453C0:01C5B552] X-archive-position: 25214 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rstauffer@decommunications.com Precedence: normal Reply-To: rstauffer@decommunications.com 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=-2.4 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 Try the following per "Specifying a Full Set of Hints" in the 9ir2 Performance Tuning Guide and Reference SELECT /*+ index(a index_A) index(b index_B) */ column1,column2,column3,column4 from table_a a,table_b b where a.something = b.something; Bob Stauffer DBA D&E Communications Ephrata, PA, USA 717-738-8737 rstauffer@decommunications.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Fred Smith Sent: Friday, September 09, 2005 11:17 To: oracle-l@freelists.org Subject: Forcing SQL query to use 2 index hints Hello all, I am attempting to force a query to use 2 index hints, but am not having any luck with finding the correct syntax. The query works, and if I add 1 index hint, the explain plan output shows that it is using the index I specify. However, I need it to use 2 hints: What is the correct syntax? SELECT /*+ index(a index_A, b index_B) */ column1,column2,column3,column4 from table_a a,table_b b where a.something = b.something; (The above is not using "index_B" .... how do I force it to use index_B?? Only index_A is being used. What is the correct syntax?) Thank you!!!!! _________________________________________________________________ Don't just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- http://www.freelists.org/webpage/oracle-l **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business. -- http://www.freelists.org/webpage/oracle-l