From oracle-l-bounce@freelists.org Mon Jul 26 10:46:52 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6QFkbt30856 for ; Mon, 26 Jul 2004 10:46: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 i6QFkR630801 for ; Mon, 26 Jul 2004 10:46:37 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7071A72CB9B; Mon, 26 Jul 2004 10:22:59 -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 01380-65; Mon, 26 Jul 2004 10:22:59 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 16C0B72CD93; Mon, 26 Jul 2004 10:22:53 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 26 Jul 2004 10:21:11 -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 7FC2372CCB6 for ; Mon, 26 Jul 2004 10:21:10 -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 01256-28 for ; Mon, 26 Jul 2004 10:21:10 -0500 (EST) Received: from asmtp-a063f33.pas.sa.earthlink.net (asmtp-a063f33.pas.sa.earthlink.net [207.217.120.149]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A0B8472CCD9 for ; Mon, 26 Jul 2004 10:20:54 -0500 (EST) Received: from user-vcaul5c.dsl.mindspring.com ([216.175.84.172] helo=TerrySutton) by asmtp-a063f33.pas.sa.earthlink.net with asmtp (Exim 4.34) id 1Bp7hW-00036t-Qw for oracle-l@freelists.org; Mon, 26 Jul 2004 08:47:42 -0700 Message-ID: <017001c47327$e427f840$6601a8c0@TerrySutton> From: "Terry Sutton" To: References: <20040726143158.4803.qmail@web42008.mail.yahoo.com> <41051E53.6040405@sun.com> Subject: Re: Index help Date: Mon, 26 Jul 2004 08:43:55 -0700 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-ELNK-Trace: 5830af7b3b91928f1aa676d7e74259b7b3291a7d08dfec79c52ed40121ad207b72c119f41c2dc59b350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 216.175.84.172 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 5996 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: terrysutton@usa.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org A key factor is how frequently different values are queried. As Dan says, if :1= 46847 then a FTS is the way to go. How often is this the case? Or is the query generally used to find "non-common" values (the other 4%). If the latter, then I'd recommend using a literal for the IDA3A5 portion of the query and have a histogram on the column. Same for codeC5; it has 3 distinct values, but if 99% are one value and the query is looking for the other two, then a literal and histograms are better used here. You need to look at the query both in terms of the data distributions and the way the query is being used by the app. --Terry ----- Original Message ----- If the value of :1 = 46847 a full table scan makes sense (96% of the rows meet the predicate condition). If the value is not 46847, then an index lookup is probably faster. This is really a catch-22. Bind variables are helping your parse times, but *may* be hurting your execution time (hard to tell without knowing how many executions use 46847 and how many do not). Without a histogram on the column, Oracle will assume an even distribution, which is incorrect. You are trapped between a rock and a hard place. If you use hard-coded values instead of binds and have a histogram on the column, the CBO should pick the proper execution plan each time. This also means that you will have unique versions of the statement in your shared pool. Sorry there is not a simple, quick answer to this one. I suggest reading Wolfgang's paper "Fallacies of the Cost-Based Optimizer" at www.centrexcc.com. He explains this issue very well. Regards, Daniel Fink M.Godlewski wrote: > Data distribution. > > IDA3A5 count(IDA3A5) > 4104 44 > 4107 156 > 4110 1 > 4111 1 > 4137 1 > 46847 4905 ---------------------------------------------------------------- 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 -----------------------------------------------------------------