From oracle-l-bounce@freelists.org Tue Jul 13 10:40:08 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6DFdb704644 for ; Tue, 13 Jul 2004 10:39:47 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6DFdP604582 for ; Tue, 13 Jul 2004 10:39:36 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6290372DE1C; Tue, 13 Jul 2004 10:19:32 -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 09973-48; Tue, 13 Jul 2004 10:19:32 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8E71172DD4C; Tue, 13 Jul 2004 10:19:31 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 13 Jul 2004 10:17:52 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 00BF372DBDA for ; Tue, 13 Jul 2004 10:17:51 -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 09208-82 for ; Tue, 13 Jul 2004 10:17:50 -0500 (EST) Received: from www.hotsos.com (hotsos.com [209.120.206.15]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 26ADA72DCC5 for ; Tue, 13 Jul 2004 10:17:44 -0500 (EST) Received: from CVMLAP02 (66.169.163.33.ts46v-12.otne2.ftwrth.tx.charter.com [66.169.163.33]) (authenticated (0 bits)) by www.hotsos.com (8.12.11/8.11.0) with ESMTP id i6DFgSNK003509; Tue, 13 Jul 2004 10:42:28 -0500 From: "Cary Millsap" To: , <"undisclosed-recipients:"@www.hotsos.com> Subject: RE: Composite Index Order VS Query Order ?? , Tuning Docs URL ?. Date: Tue, 13 Jul 2004 10:40:50 -0500 Message-ID: <009501c468ef$c96d1c20$6701a8c0@CVMLAP02> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4970 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: cary.millsap@hotsos.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 1. No. The order of keys in a composite index does NOT need to be in the same order as the ANDed predicates in your WHERE clause. Now, if you = have an ORDER BY clause, it will speed your query if the columns there and the composite index columns are listed in the same order (because it will = save a sort operation). 2. Check Dan Tow's "Tuning SQL" book, published by O'Reilly and = Associates. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 7/20 Cleveland, 8/10 Boston, 9/14 San = Francisco - SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 = Hartford - Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org] On Behalf Of Ranjeesh K R. Sent: Tuesday, July 13, 2004 9:54 AM To: undisclosed-recipients: Subject: Composite Index Order VS Query Order ?? , Tuning Docs URL ?. Hi, I was working on optimising a piece of code which was taking 10hrs + to execute and all.So my queries are.. #1) Saw a table where the "order of query" on the table and the "order = of key" are different . For faster results shouldn't they be in the same order. for eg: Select ... where A.ID =3D ... and A.Name =3D ... The Key order on this table A NOW is Name & ID Order .=20 Shouldn't they be in the order ID , Name ?.. #2) Can anybody recommend me any site , where I will get reliable information on Tuning PL/SQL ?. With thanks in advance=20 Ranjeesh ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------