From oracle-l-bounce@freelists.org Fri Jul 23 14:06:40 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6NJ6Om04346 for ; Fri, 23 Jul 2004 14:06:34 -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 i6NJ6E604297 for ; Fri, 23 Jul 2004 14:06:24 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E842D72C344; Fri, 23 Jul 2004 13:45:12 -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 22493-61; Fri, 23 Jul 2004 13:45:12 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2CC7A72D193; Fri, 23 Jul 2004 13:45:12 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 23 Jul 2004 13:43:40 -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 7146872C9F7 for ; Fri, 23 Jul 2004 13:43:39 -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 22200-43 for ; Fri, 23 Jul 2004 13:43:39 -0500 (EST) Received: from tera.umi.com (tera.umi.com [192.195.245.144]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 101A172C087 for ; Fri, 23 Jul 2004 13:43:39 -0500 (EST) Received: from aabo-exchange04.bos.il.pqe (aabo-exchange04.bos.il.pqe [172.24.3.67]) by tera.umi.com (8.12.10/8.12.10) with ESMTP id i6NJ9hM4028197 for ; Fri, 23 Jul 2004 15:09:43 -0400 Received: from bosmail00.bos.il.pqe ([172.24.3.64]) by aabo-exchange04.bos.il.pqe with Microsoft SMTPSVC(6.0.3790.0); Fri, 23 Jul 2004 15:09:50 -0400 X-MIMEOLE: Produced By Microsoft Exchange V6.0.6556.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit Subject: RE: Why does this query need a table access by rowid? Date: Fri, 23 Jul 2004 15:09:49 -0400 Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660C2B@bosmail00.bos.il.pqe> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Why does this query need a table access by rowid? Thread-Index: AcRw55zwuMyC1dtIS0uDh0ZVOiIC0gAAMgfw From: "Bobak, Mark" To: X-OriginalArrivalTime: 23 Jul 2004 19:09:50.0203 (UTC) FILETIME=[A0948CB0:01C470E8] X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 5901 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Mark, That's true, but it would require the index to be much larger. The idea behind the function based index was to reduce the size of the index, to make it for cache-friendly. -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Post Hoc Ergo Propter Hoc" -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Powell, Mark D Sent: Friday, July 23, 2004 2:59 PM To: 'oracle-l@freelists.org' Subject: RE: Why does this query need a table access by rowid? If you only need to find out if a document is in hold status on a case by case basis then couldn't you also accomplish this same goal by using a nonunique index built on (doc_id,doc_hold_status) to support the PK constraint on doc_id. Only an index access would be necessary to verify the status of the document and you could dispense with use of a function based index. This would not work as well for finding all documents on hold but it should work for testing individual documents. HTH -- Mark D Powell -- -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Bobak, Mark Sent: Friday, July 23, 2004 2:41 PM To: oracle-l@freelists.org Subject: RE: Why does this query need a table access by rowid? Actually, it looks like the ultimate solution will be: Select :b1 from documents doc where decode(doc.doc_hold_status,'Y',doc_id,null) =3D3D :b1; Since the input is a single doc_id, if a row is returned, I *know* it's the doc_id I asked for, and this avoids the table access. Thanks again for the help, all. -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Post Hoc Ergo Propter Hoc" -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Mark W. Farnham Sent: Friday, July 23, 2004 2:25 PM To: oracle-l@freelists.org Subject: RE: Why does this query need a table access by rowid? So: SELECT /*+ index(doc doc_on_hold) */ DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) FROM DOCUMENTS doc WHERE DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) =3D3D:B1; should either work as you wish(ie. skip the table access) or not. Since you're about to try that, I'll refrain from testing it myself.... mwf -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Karen Morton Sent: Friday, July 23, 2004 2:10 PM To: oracle-l@freelists.org Subject: RE: Why does this query need a table access by rowid? I believe the table is accessed because the index contains doc_id per the FBI definition. DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) is not the same as DOC_ID. Therefore, the table access is required to get plain old DOC_ID. Karen Morton Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events at http://www.hotsos.com/education/schedule.html -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Bobak, Mark Sent: Friday, July 23, 2004 10:56 AM To: oracle-l@freelists.org Subject: Why does this query need a table access by rowid? Hi, I've got a table, DOCUMENTS, with lots of columns (and lots of rows). DOC_ID is the PK, and DOC_HOLD_STATUS is another column in the table, which is NOT NULL and will always have 'Y' or 'N'. Now, I've got a query where, given the DOC_ID, I want to determine if a particular document is on hold. Note that DOCUMENTS contains on the order of 170M rows, of which approximately 200k rows are flagged as DOC_HOLD_STATUS=3D3D3D'Y', or, docs that are on hold. So, given the large disparity in the number of docs on hold vs. not on hold, I created a function-based index defined as: Create DOC_ON_HOLD on DOCUMENTS(DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL)); Now, that gives me a convenient index of just the docs that are on hold. (And with only around 200k DOC_IDs, it's *much* smaller than if I just had an index on DOC_HOLD_STATUS, which makes it a lot more cache friendly.) So, finally, I get to the problem. When I execute this query: SELECT /*+ index(doc doc_on_hold) */ DOC_ID=3D3D20 FROM DOCUMENTS doc=3D3D20 WHERE DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) =3D3D3D :B1; I get this execution plan: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3D3D3DCHOOSE (Cost=3D3D3D1 =3D Card=3D3D3D1430947 Bytes=3D3D3D10016629) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTS' (Cost=3D3D3D1 Card=3D3D3D1430947 Bytes=3D3D3D10016629) 2 1 INDEX (RANGE SCAN) OF 'DOC_ON_HOLD' (NON-UNIQUE) =3D (Cost=3D3D3D3 Card=3D3D3D1430947) 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? Thanks, -Mark PS Granted, this is an efficient query, and the elimination of the table access amounts to one less consistent get. However, this is a VERY heavily hit query, and reducing 4 or 5 consistent gets to 3 or 4 could potentially be a big savings on a query that gets executed as much as this one does. Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Post Hoc Ergo Propter Hoc" ---------------------------------------------------------------- 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 -----------------------------------------------------------------