| 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
D'oh!
(Mark slaps his forehead!)
I didn't forget to mention it, I failed to realize it! This is an application which we recently acquired via corporate acquisition, and I'm not at all familiar with the data model. It never even dawned on me to look for or think of an IOT! Now that you've pointed that out, at least I understand how that access path is possible, if not why it was being taken. As I mentioned, analyzing the table solved the problem, to me the access path was a curiosity.
Thanks Jonathan!
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Wednesday, June 09, 2004 4:07 PM
To: oracle-l_at_freelists.org
Subject: 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"
-- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 15:34:37 CDT
 |
 |