From oracle-l-bounce@freelists.org Tue Sep 20 12:33:02 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j8KHX2Tc004231 for ; Tue, 20 Sep 2005 12:33:02 -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 j8KHWu6H004198 for ; Tue, 20 Sep 2005 12:32:56 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6FEE71EEF51; Tue, 20 Sep 2005 12:30:06 -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 23298-09; Tue, 20 Sep 2005 12:30:06 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E10281EE57F; Tue, 20 Sep 2005 12:30:05 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.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: Index skip scan Date: Tue, 20 Sep 2005 13:27:17 -0400 Message-ID: <4001DEAF7DF9BD498B58B45051FBEA6502C8C02E@25exch1.vicorpower.vicr.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Index skip scan thread-index: AcW+B6kUvdqsDDrtR7yB70UM/D0rTAAANmKg From: "Goulet, Dick" To: , Cc: X-OriginalArrivalTime: 20 Sep 2005 17:27:17.0548 (UTC) FILETIME=[8C75E6C0:01C5BE08] X-archive-position: 25704 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: DGoulet@vicr.com Precedence: normal Reply-To: DGoulet@vicr.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=-3.5 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 Provided your using CBO. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Wolfgang Breitling Sent: Tuesday, September 20, 2005 1:19 PM To: Amir.Hameed@xerox.com Cc: oracle-l@freelists.org Subject: Re: Index skip scan The answer is similar to the one for the question "What is the % of rows where the optimizer switched from an index access to a full scan, or vice versa". There is not fixed threshold. The optimizer with cost the various possible access paths and choose the one the comes out with the lowest cost. BTW, skip scan is not limited to just skipping one leading column. You can get an index skip-scan with predicates missing more than one leading column of the index. Quoting "Hameed, Amir" : > Folks, > Does anyone know that in order for the optimizer to choose "Index skip > scan" on a concatenated index where the first column is not very > selective but the second one is, approximately what type of selectivity > on the leading column the Optimizer look for before deciding to take > this route? -- regards Wolfgang Breitling Oracle 7,8,8i,9i OCP DBA Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l