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