RE: Odd execution plan

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 9 Jun 2004 18:49:54 -0400
Message-ID: <42BBD772AC30EA428B057864E203C999274291@MSGBOSCLF2WIN.DMN1.FMR.COM>

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_at_il.proquest.com] Sent: Wednesday, June 09, 2004 3:09 PM
To: oracle-l_at_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_at_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_at_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

Received on Wed Jun 09 2004 - 17:46:43 CDT