RE: Optimizer sees index, but ignores it in access path decision process
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "Hemant K Chitale" <hkchital@xxxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Oct 2007 15:26:07 -0400
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@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: Hemant K Chitale [mailto:hkchital@xxxxxxxxxxxxxx]
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@xxxxxxxxxxxxxxxxx
><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
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
