RE: Same index, different plan
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Apr 2008 14:41:40 -0400
How about because the OP told Oracle to scan the index via the INDEX
hint
>> The INDEX hint instructs the optimizer to use an index scan for the
specified table <<
From Oracle(r) Database SQL Reference 10g Release 2 (10.2) Part Number
B14200-01 entry for INDEX Hint.
-- Mark D Powell --
Phone (313) 592-5148
-----Original Message-----
From: Baumgartel, Paul [mailto:paul.baumgartel@xxxxxxxxxxxxxxxxx]
Sent: Friday, April 04, 2008 2:27 PM
To: Powell, Mark D; oracle-l
Subject: RE: Same index, different plan
All true, but the question is why a hint that says "use this index"
produces a different type of scan than a query without the hint that
uses the index anyway.
Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D
Sent: Friday, April 04, 2008 1:01 PM
To: oracle-l
Subject: RE: Same index, different plan
There is an index hint for Fast Full Scan as well as one to stop the CBO
from using a FFS:
index_ffs
no_index_ffs
Also on a non-unique index isn't the normal access always a range scan,
potentially bounded or unbounded, but still a range scan.
-- Mark D Powell --
Phone (313) 592-5148
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hemant K Chitale
Sent: Friday, April 04, 2008 12:23 PM
To: paul.baumgartel@xxxxxxxxxxxxxxxxx; oracle-l
Subject: Re: Same index, different plan
Possibly the last time statistics were gathered, the "high value" for
the column (HIGH_VALUE in USER_TAB_COLUMNS) was lesser than today's
SYSDATE so Oracle thought a Fast Full Scan would suffice.
However, when you Hint to Oracle to use the Index, Oracle now believes
that it has to do a Range Scan ?
Hemant K Chitale
http://hemantoracledba.blogspot.com
"There is more to life than increasing its speed."
Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
At 10:27 PM Friday, Baumgartel, Paul wrote:
>A colleague is asking for help with a 10.2.0.3 (Sun Solaris 8)
>optimizer issue. A unhinted query
>
>SELECT max(history_timestamp) AS MaxStamp FROM
>dmsdb.t_dms_deal_security_xref_h WHERE history_timestamp<sysdate+1 AND
>eod_snapshot='N';
>
>yields the plan
>
>-----------------------------------------------------------------------
>----------------------
>
>| Id | Operation | Name |
>Rows | Bytes | Cost (%CPU)|
>-----------------------------------------------------------------------
>----------------------
>
>| 0 | SELECT
>STATEMENT | | 1 | 13 | 86560
(18)|
>| 1 | SORT
>AGGREGATE | | 1 | 13
>| |
>| 2 | INDEX FAST FULL SCAN| T_DMS_DEAL_SECURITY_XREF_H_AK1
>| 94M| 1168M| 86560 (18)|
>-----------------------------------------------------------------------
>----------------------
>
>
>and runs in about 5 minutes.
>
>However, the same query, with a hint specifying the index that's being
>used for the non-hinted version, yields
>
>-----------------------------------------------------------------------
>------------------
>
>| Id | Operation | Name | Rows |
>Bytes | Cost (%CPU)|
>-----------------------------------------------------------------------
>------------------
>
>| 0 | SELECT STATEMENT | | 1
>| 13 | 330K (1)|
>| 1 | SORT AGGREGATE | | 1
>| 13 | |
>| 2 | INDEX RANGE SCAN| T_DMS_DEAL_SECURITY_XREF_H_AK1
>| 94M| 1168M| 330K (1)|
>-----------------------------------------------------------------------
>------------------
>
>
>and runs in about 3 minutes. Note that the access path has changed
>from fast full scan to range scan, and note the higher cost of the ran
>
>Why would hinting to use an index that's being used anyway change the
>access path from fast full scan to range scan?
>
>
>Paul Baumgartel
>CREDIT SUISSE
>Information Technology
>Prime Services Databases Americas
>One Madison Avenue
>New York, NY 10010
>USA
>Phone 212.538.1143
>paul.baumgartel@xxxxxxxxxxxxxxxxx
><file://www.credit-suisse.com>www.credit-suisse.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
========================================================================
======
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
========================================================================
======
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Same index, different plan
- » RE: Same index, different plan
- » Re: Same index, different plan
- » RE: Same index, different plan
- » RE: Same index, different plan
- » RE: Same index, different plan
- » Re: Same index, different plan
- » RE: Same index, different plan
- » RE: Same index, different plan
- » RE: Same index, different plan
