RE: Same index, different plan
Date: Fri, 4 Apr 2008 14:27:07 -0400
Message-ID: <21469B88E0EA11498818517F2103353101C661A4@EPRI17P32001A.csfb.cs-group.com>
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_at_credit-suisse.com
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale
Sent: Friday, April 04, 2008 12:23 PM
To: paul.baumgartel_at_credit-suisse.com; 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_at_credit-suisse.com
><file://www.credit-suisse.com>www.credit-suisse.com
-- http://www.freelists.org/webpage/oracle-l -- http://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 ============================================================================== -- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 04 2008 - 13:27:07 CDT