Mark, You forgot to mention that the table was an IOT. It's still looks odd that the default path used that index, but I guess there was something about the stats on the PK perhaps an extreme clustering factor) combined with a relatively small flag index that made it happen: Test case: drop table hwc_asi_topic_doc; create table hwc_asi_topic_doc( DOC_ID NUMBER(10) not null, TOPIC_ID NUMBER(10) not null, SCORE NUMBER(4), COVERED NUMBER(1), FLAG VARCHAR2(1), GRADE VARCHAR2(1), constraint HWC_ASI_TOPIC_DOC_PK primary key (doc_id, topic_id) ) organization index ; create index HATD_FLAG_INDX01 on hwc_asi_topic_doc(flag); set autotrace traceonly explain select /*+ first_rows index(td HATD_FLAG_INDX01) */ topic_id, score from hwc_asi_topic_doc td where doc_id = 36349537 and flag='R'; set autotrace off Output: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3 Card=1 Bytes=41) 1 0 INDEX (UNIQUE SCAN) OF 'HWC_ASI_TOPIC_DOC_PK' (UNIQUE) (Cost=1 Card=1 Bytes=41) 2 1 INDEX (RANGE SCAN) OF 'HATD_FLAG_INDX01' (NON-UNIQUE) (Cost=1 Card=1) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st ----- Original Message ----- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Wednesday, June 09, 2004 8:08 PM 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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------