| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Odd execution plan
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) )
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/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
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
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"
 |
 |