From oracle-l-bounce@freelists.org Wed Jun 9 17:46:43 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i59MkSD14157 for ; Wed, 9 Jun 2004 17:46:38 -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 i59MkI614138 for ; Wed, 9 Jun 2004 17:46:28 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6E19B72CAA8; Wed, 9 Jun 2004 17:31:53 -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 17015-64; Wed, 9 Jun 2004 17:31:53 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A927C72CA9E; Wed, 9 Jun 2004 17:31:52 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 09 Jun 2004 17:30:33 -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 C968072C880 for ; Wed, 9 Jun 2004 17:30:32 -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 17015-46 for ; Wed, 9 Jun 2004 17:30:32 -0500 (EST) Received: from maillnx-us312.fmr.com (maillnx-us312.fmr.com [192.223.178.27]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6EE1472C8D6 for ; Wed, 9 Jun 2004 17:30:32 -0500 (EST) Received: from virmmk110nts.fmr.com (virmmk110nts.fmr.com [172.25.107.117]) by maillnx-us312.fmr.com (Switch-3.1.2/Switch-3.1.0) with SMTP id i59Mnt9p023719 for ; Wed, 9 Jun 2004 18:49:55 -0400 Received: from MSGBOSCLF2WIN.fmr.com ([10.46.17.174]) by MSGMROIM01WIN.DMN1.FMR.COM with Microsoft SMTPSVC(5.0.2195.6713); Wed, 9 Jun 2004 18:49:54 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.0.6556.0 content-class: urn:content-classes:message Subject: RE: Odd execution plan 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 Date: Wed, 9 Jun 2004 18:49:54 -0400 Message-ID: <42BBD772AC30EA428B057864E203C999274291@MSGBOSCLF2WIN.DMN1.FMR.COM> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Odd execution plan Thread-Index: AcROVXT59q8nSXnwSlOPIQghs+yS4QAHngvA From: "Khedr, Waleed" To: X-OriginalArrivalTime: 09 Jun 2004 22:49:54.0881 (UTC) FILETIME=[15018310:01C44E74] X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2435 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Waleed.Khedr@FMR.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I don't see table fetch by rowid in the Exec. Plan to get the columns in the select clause, is the table an IOT? Waleed -----Original Message----- From: Bobak, Mark [mailto:Mark.Bobak@il.proquest.com] Sent: Wednesday, June 09, 2004 3:09 PM To: oracle-l@freelists.org Subject: Odd execution plan Hi, I'm on Solaris 8 and Oracle 8.1.7.4. I have a query of the form: select topic_id, score from hwc_asi_topic_doc td where doc_id = 36349537 and flag='R'; For a table that looks like: SQL> desc hwc_asi_topic_doc Name Null? Type ----------------------------------------- -------- ---------------------------- DOC_ID NOT NULL NUMBER(10) TOPIC_ID NOT NULL NUMBER(10) SCORE NUMBER(4) COVERED NUMBER(1) FLAG VARCHAR2(1) GRADE VARCHAR2(1) And is indexed as such: INDEX_NAME COLUMN_NAME -------------------- ------------------------------ HATD_FLAG_INDX01 FLAG HATD_GRADE_INDX02 GRADE HWC_ASI_TOPIC_DOC_PK DOC_ID HWC_ASI_TOPIC_DOC_PK TOPIC_ID I got a complaint that the query was running slow. So, first thing, I did a quick set autotrace traceonly exp and I got a somewhat odd looking plan: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=48 Bytes= 1968) 1 0 INDEX (UNIQUE SCAN) OF 'HWC_ASI_TOPIC_DOC_PK' (UNIQUE) (Co st=2289 Card=48 Bytes=1968) 2 1 INDEX (RANGE SCAN) OF 'HATD_FLAG_INDX01' (NON-UNIQUE) (C ost=2289 Card=48) I don't think I've ever seen this before, where an index scan feed another index scan on the same table....I tried a no_index() hint on hatd_flag_indx01, and that solved the problem. (From 127563 buffer gets down to 3 buffer gets.) I then analyzed the table and that solved the problem w/o the hint. However, I was just curious, as I don't think I've ever seen the optimizer utilize a plan such as this before. I have to wonder, how (via hints) can I control the order the indexes are accessed? I tried specifying individual index() hints, one for each index, and changing the order, but as soon as I specify the hwc_asi_topic_doc_pk index, it's the only one used. It doesn't matter that I specified both indexes or what order they're in. Anyhow, I just thought this was kind of curious..... -Mark 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 -----------------------------------------------------------------