| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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"
 |
 |