From oracle-l-bounce@freelists.org Tue Jul 13 10:14:02 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6DFDbb30616 for ; Tue, 13 Jul 2004 10:13: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 i6DFDR630560 for ; Tue, 13 Jul 2004 10:13:37 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9CD1072C28E; Tue, 13 Jul 2004 09:53:31 -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 01695-96; Tue, 13 Jul 2004 09:53:31 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CB71672C283; Tue, 13 Jul 2004 09:53:30 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 13 Jul 2004 09:51:53 -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 8889072DBB9 for ; Tue, 13 Jul 2004 09:51: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 01695-32 for ; Tue, 13 Jul 2004 09:51:51 -0500 (EST) Received: from troll.tpk.net (mail.tpk.net [216.107.198.11]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8953F72DB24 for ; Tue, 13 Jul 2004 09:51:43 -0500 (EST) Received: from MWF600XL (host216-107-212-43.tpk.net [216.107.212.43]) by troll.tpk.net (Postfix) with SMTP id 2568E669991 for ; Tue, 13 Jul 2004 11:16:28 -0400 (EDT) From: "Mark W. Farnham" To: Subject: RE: Composite Index Order VS Query Order ?? , Tuning Docs URL ?. Date: Tue, 13 Jul 2004 11:16:42 -0400 Message-ID: MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 In-Reply-To: X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4968 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org On question #1. Presuming you're CBO and you have decent stats and don't run out of permutations, text editting of the sql other than hints and getting your query to exactly match an already parsed query in the pool *should* have no influence on the final plan. Now *unproven theory* I posit that Oracle could-should interrupt the cost compilation of various alternative plans as soon as the cost gets to a tie or worse with the existing best plan, so *if parse-permutation time is significant in your use*, then if we knew how to influence Oracle to try plans in some order without hamstringing the result, we might shave a bit off. Then we'll go through an age of new mythology on how to influence plans and ultimately Oracle will start allowing us to save outlines as optimizer starting points to beat so that the mythology will evaporate. This might even happen automatically. Maybe they'll even swap permutations for permute time. I have no known situations where this would actually improve database operational efficiency, but I'd bet a dozen donuts there are. In short, the query text should not influence the plan you get, but it could possibly change how long it takes to get the winning plan. If you use hints or too few permutations, you may get a different plan. IF you're RBO, all sorts of things can affect the plan you get. Maybe Wolfgang has thought about this? Or others? On question #2 I hesitate to re-start a thread on all those good URLs and I suggest you seach the very recent BOOKS thread (which was mispelled BOO). mwf -----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 10: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 = ... and A.Name = ... The Key order on this table A NOW is Name & ID Order . 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 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 -----------------------------------------------------------------