RE: Optimizer sees index, but ignores it in access path decision process

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Wed, 3 Oct 2007 15:26:07 -0400
Message-ID: <21469B88E0EA11498818517F21033531597009@EPRI17P32001A.csfb.cs-group.com>

The stats are up to date. Yes, all of the dates in the column have time = 00:00:00.

Still, the optimizer trace shows that this index was not even considered; I'm also unable to get the index to be used via a hint. No matter how bad the index, shouldn't it be considered?

Paul Baumgartel
CREDIT SUISSE
Information Technology
Securities Processing 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: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg] Sent: Saturday, September 29, 2007 9:25 AM To: Baumgartel, Paul; oracle-l
Subject: Re: Optimizer sees index, but ignores it in access path decision process

For

Index: NUK_TCONTRACT_TRD_DT Col#: 35

     USING COMPOSITE STATS
     LVLS: 3  #LB: 402118  #DK: 80  LB/K: 5026.00  DB/K:

52071.00 CLUF: 4165752.00 The Distinct Keys are only 80 and Leaf Blocks per Key is a very high 5,026.
If the indexed column is a date are all the values inserted into the table as TRUNC(SYSDATE) ?
Or does Oracle have the wrong statistics for the index ?

Hemant

At 04:35 AM Saturday, Baumgartel, Paul wrote:

>Oracle 10.2.0.3 on Solaris.
>
>Table is range-partitioned on TCONTRACT_TRADE_DATE. Query is
>
>select
>min(TCONTRACT_TRADE_DATE)
>from ods.ods_tcontract;
>
>A single-column partitioned index (NUK_TCONTRACT_TRD_DT) exists on
>TCONTRACT_TRADE_DATE. Another (composite) index contains that
>column in position 2. Optimizer chooses the second index every
>time, even when a hint specifies the first one.
>
>Optimizer (event 10053) trace shows awareness of the preferred index:
>
>Index Stats::
> Index: NUK_TCONTRACT_BUSDATE_CUSIP Col#: 52 20
> USING COMPOSITE STATS
> LVLS: 3 #LB: 713238 #DK: 384265 LB/K: 1.00 DB/K:
> 86.00 CLUF: 33203958.00
> Index: NUK_TCONTRACT_CUSIP Col#: 20 35 57 1
> USING COMPOSITE STATS
> LVLS: 3 #LB: 955919 #DK: 112199564 LB/K: 1.00 DB/K:
> 1.00 CLUF: 43217198.00
> Index: NUK_TCONTRACT_TRD_DT Col#: 35
> USING COMPOSITE STATS
> LVLS: 3 #LB: 402118 #DK: 80 LB/K: 5026.00 DB/K:
> 52071.00 CLUF: 4165752.00
>
>But, in next section of trace, in which access paths are considered,
>NUK_TCONTRACT_TRD_DT does not appear at all (see below). How can that
be?
>
>Paul Baumgartel
>CREDIT SUISSE
>Information Technology
>Securities Processing 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
>
>
>SINGLE TABLE ACCESS PATH
> Table: ODS_TCONTRACT Alias: ODS_TCONTRACT
> Card: Original: 115569516 Rounded: 115569516 Computed:
> 115569516.00 Non Adjusted: 115569516.00
> Access Path: TableScan
> Cost: 7280284.59 Resp: 7280284.59 Degree: 0
> Cost_io: 7028169.00 Cost_cpu: 193577124940
> Resp_io: 7028169.00 Resp_cpu: 193577124940
> Access Path: index (index (FFS))
> Index: NUK_TCONTRACT_CUSIP
> resc_io: 489938.00 resc_cpu: 20271467483
> ix_sel: 0.0000e+00 ix_sel_with_filters: 1
> Access Path: index (FFS)
> Cost: 516339.64 Resp: 516339.64 Degree: 1
> Cost_io: 489938.00 Cost_cpu: 20271467483
> Resp_io: 489938.00 Resp_cpu: 20271467483
> Access Path: index (index (FFS))
> Index: UK_TCONTRACT_TICKER
> resc_io: 364308.00 resc_cpu: 18525881475
> ix_sel: 0.0000e+00 ix_sel_with_filters: 1
> Access Path: index (FFS)
> Cost: 388436.18 Resp: 388436.18 Degree: 1
> Cost_io: 364308.00 Cost_cpu: 18525881475
> Resp_io: 364308.00 Resp_cpu: 18525881475
> Access Path: index (FullScan)
> Index: NUK_TCONTRACT_CUSIP
> resc_io: 955985.00 resc_cpu: 29247902618
> ix_sel: 1 ix_sel_with_filters: 1
> Cost: 864847.50 Resp: 864847.50 Degree: 1
> Access Path: index (FullScan)
> Index: UK_TCONTRACT_TICKER
> resc_io: 710868.00 resc_cpu: 27502316610
> ix_sel: 1 ix_sel_with_filters: 1
> Cost: 649617.80 Resp: 649617.80 Degree: 1
> ****** trying bitmap/domain indexes ******
> Access Path: index (FullScan)
> Index: NUK_TCONTRACT_CUSIP
> resc_io: 955985.00 resc_cpu: 29247902618
> ix_sel: 1 ix_sel_with_filters: 1
> Cost: 864847.50 Resp: 864847.50 Degree: 0
> Access Path: index (FullScan)
> Index: UK_TCONTRACT_TICKER
> resc_io: 710868.00 resc_cpu: 27502316610
> ix_sel: 1 ix_sel_with_filters: 1
> Cost: 649617.80 Resp: 649617.80 Degree: 0
>******** Begin index join costing ********
> ****** trying bitmap/domain indexes ******
> Access Path: index (FullScan)
> Index: NUK_TCONTRACT_CUSIP
> resc_io: 955985.00 resc_cpu: 29247902618
> ix_sel: 1 ix_sel_with_filters: 1
> Cost: 864847.50 Resp: 864847.50 Degree: 0
> Access Path: index (FullScan)
> Index: UK_TCONTRACT_TICKER
> resc_io: 710868.00 resc_cpu: 27502316610
> ix_sel: 1 ix_sel_with_filters: 1
> Cost: 649617.80 Resp: 649617.80 Degree: 0
> Access Path: index (FullScan)
> Index: UK_TCONTRACT_TICKER
> resc_io: 710868.00 resc_cpu: 27502316610
> ix_sel: 1 ix_sel_with_filters: 1
> Cost: 649617.80 Resp: 649617.80 Degree: 0
>******** End index join costing ********
> Best:: AccessPath: IndexFFS Index: UK_TCONTRACT_TICKER
> Cost: 388436.18 Degree: 1 Resp: 388436.18 Card:
> 115569516.00 Bytes: 0
>
>
>=======================================================================

>Please access the attached hyperlink for an important electronic
>communications disclaimer:
>
>http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>=======================================================================

Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
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

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 Wed Oct 03 2007 - 14:26:07 CDT