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

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: paul.baumgartel@xxxxxxxxxxxxxxxxx, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 29 Sep 2007 21:24:59 +0800


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 

--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2007