From oracle-l-bounce@freelists.org Fri Jul 23 13:14:13 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6NIDwR26745 for ; Fri, 23 Jul 2004 13:14:08 -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 i6NIDm626711 for ; Fri, 23 Jul 2004 13:13:58 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BF27772CC19; Fri, 23 Jul 2004 12:52:46 -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 11652-18; Fri, 23 Jul 2004 12:52:46 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1927572C707; Fri, 23 Jul 2004 12:52:46 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 23 Jul 2004 12:51:20 -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 CD91172C0A7 for ; Fri, 23 Jul 2004 12:51:19 -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 10234-94 for ; Fri, 23 Jul 2004 12:51:19 -0500 (EST) Received: from MXR-1.estpak.ee (ld3.estpak.ee [194.126.101.102]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 76F8F72C06A for ; Fri, 23 Jul 2004 12:51:19 -0500 (EST) Received: from porgand (217-159-150-133-dsl.kvm.estpak.ee [217.159.150.133]) by MXR-1.estpak.ee (Postfix) with SMTP id E4D1C50F5F for ; Fri, 23 Jul 2004 21:17:39 +0300 (EEST) Message-ID: <357001c470e1$55bf9d30$0a879fd9@porgand> From: =?ISO-8859-1?Q?Tanel_P=F5der?= To: References: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660C27@bosmail00.bos.il.pqe> Subject: Re: Why does this query need a table access by rowid? Date: Fri, 23 Jul 2004 21:17:29 +0300 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-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at neti.ee X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 5887 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tanel.poder.003@mail.ee Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org > So, the index is recognized, and that's great. My question is, why is > the TABLE ACCESS BY ROWID required? > Since only the DOC_ID is in the select list, why the table access? Since in the index you don't have the DOC_ID column, you have only the function of DOC_ID in your index and Oracle isn't smart enough to look inside the function to see whether the funcion leaves the value unaltered... Try to do a composite index on f(doc_id), doc_id. Tanel. ---------------------------------------------------------------- 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 -----------------------------------------------------------------