RE: Same index, different plan

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 4 Apr 2008 13:00:46 -0400

 
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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2008