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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: