RE: Odd execution plan
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Jun 2004 21:16:15 -0400
Waleed,
Yep, as Jonathan correctly identified, the table was an IOT, and I had
overlooked that fact.
Thanks,
-Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Khedr, Waleed
Sent: Wed 6/9/2004 6:49 PM
To: oracle-l@xxxxxxxxxxxxx
Cc:
Subject: RE: Odd execution plan
I don't see table fetch by rowid in the Exec. Plan to get the columns in
the select clause, is the table an IOT?
Waleed
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Wednesday, June 09, 2004 3:09 PM
To: oracle-l@xxxxxxxxxxxxx
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
-----------------------------------------------------------------
Other related posts:
