For Index: NUK_TCONTRACT_TRD_DT Col#: 35 USING COMPOSITE STATSLVLS: 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 STATSLVLS: 3 #LB: 713238 #DK: 384265 LB/K: 1.00 DB/K: 86.00 CLUF: 33203958.00Index: NUK_TCONTRACT_CUSIP Col#: 20 35 57 1 USING COMPOSITE STATSLVLS: 3 #LB: 955919 #DK: 112199564 LB/K: 1.00 DB/K: 1.00 CLUF: 43217198.00Index: NUK_TCONTRACT_TRD_DT Col#: 35 USING COMPOSITE STATSLVLS: 3 #LB: 402118 #DK: 80 LB/K: 5026.00 DB/K: 52071.00 CLUF: 4165752.00But, 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_TCONTRACTCard: Original: 115569516 Rounded: 115569516 Computed: 115569516.00 Non Adjusted: 115569516.00Access 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_TICKERCost: 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