RE: Same index, different plan

From: <oracle-l-bounce_at_freelists.org>
Date: Fri, 4 Apr 2008 14:41:40 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A9022AA6FB@usahm208.amer.corp.eds.com>


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_at_credit-suisse.com] 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_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-l
Received on Fri Apr 04 2008 - 13:41:40 CDT

Original text of this message