Re: optimizer strange index decision

The stats may be up to date, but they're not accurate enough. An index on (col2) cannot possibly have more distinct keys that an index on (col2, col3).

The difference in cost between the two indexes is tiny but consistent with the size of the error in the two sets of index stats. As a quick check test you could do a compute / 100% sample on the two indexes to see if the change the choice.

Regards
Jonathan Lewis

On Thu, 17 Mar 2022 at 14:43, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Hello everyone,
>
> I have a situation where a table has 2 indexes, one index on a column
> which is used usually in joins and another composite index on 2 columns
> with the leading column the one used in joins.
>
> Based on above the normal choice will be any time when this table is
> joined via a NL to use the index on the single column and not the composite
> one, but of course in my situation is not happening this and I cannot
> figure it out why. Based on below the CPU cost is slightly lower for the
> composite one:
>
> Stats are up to date for table and indexes. The version is 12.1. Any idea
> what can be the issue here?
>
> Thanks a lot.
>
> Index Stats::
>
> Index: WRONG_INDEX Col#: 2 3
>
> LVLS: 2 #LB: 11009 #DK: 1344098 LB/K: 1.00 DB/K: 1.00 CLUF:
> 1291070.00 NRW: 1344098.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR:
> 0.00 KQDFLG: 1 BSZ: 1
>
> KKEISFLG: 1
>
> Index: RIGHT_INDEX Col#: 2
>
> LVLS: 2 #LB: 5926 #DK: 1346451 LB/K: 1.00 DB/K: 1.00 CLUF:
> 1289850.00 NRW: 1346451.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR:
> 0.00 KQDFLG: 1 BSZ: 1
>
> KKEISFLG: 1
>
>
>
> ***************
>
> Now joining: TABLE2[PA]#1
>
> ***************
>
> *NL Join*
>
> Outer table: Card: 880.239436 Cost: 130366373.435402 Resp:
> 130366373.435402 Degree: 1 Bytes:
>
> Access path analysis for TABLE2
>
> Scan IO Cost (Disk) = 7983.355682
>
> Scan CPU Cost (Disk) = 577749676.880000
>
> Column (#4):
>
> NewDensity:0.000002, OldDensity:0.000002 BktCnt:5699.000000,
> PopBktCnt:638.000000, PopValCnt:15, NDV:581248
>
> Column (#4): TABLE1_COL4(VARCHAR2)
>
> AvgLen: 14 NDV: 581248 Nulls: 0 Density: 0.000002
>
> Histogram: Hybrid #Bkts: 254 UncompBkts: 5699 EndPtVals: 254
> ActualVal: yes
>
> Total Scan IO Cost = 7983.355682 (scan (Disk))
>
> + 0.000000 (io filter eval) (= 0.000000 (per row)
> * 1362337.000000 (#rows))
>
> = 7983.355682
>
> Total Scan CPU Cost = 577749676.880000 (scan (Disk))
>
> + 68117688.325578 (cpu filter eval) (= 50.000615
> (per row) * 1362337.000000 (#rows))
>
> = 645867365.205578
>
> * Inner table: TABLE2 Alias: PA*
>
> Access Path: TableScan
>
> NL Join: Cost: 137406040.135845 Resp: 137406040.135845 Degree: 1
>
> Cost_io: 135725125.000000 Cost_cpu: 66745175095067
>
> Resp_io: 135725125.000000 Resp_cpu: 66745175095067
>
> ******* Costing Index WRONG_INDEX*
>
> SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
>
>
>
> SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
>
>
>
> Access Path: index (RangeScan)
>
> Index: WRONG_INDEX
>
> resc_io: 3.000000 *resc_cpu: 23522*
>
> ix_sel: 7.3403e-07 ix_sel_with_filters: 7.3403e-07
>
> NL Join : Cost: 130369013.956686 Resp: 130369013.956686 Degree: 1
>
> Cost_io: 128702412.000000 *Cost_cpu: 66176832512680*
>
> Resp_io: 128702412.000000 Resp_cpu: 66176832512680
>
> ******* Costing Index RIGHT_INDEX*
>
> SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
>
> SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
>
> Access Path: index (AllEqJoinGuess)
>
> Index: RIGHT_INDEX
>
> resc_io: 3.000000 *resc_cpu: 23523*
>
> ix_sel: 7.3403e-07 ix_sel_with_filters: 7.3403e-07
>
> NL Join : Cost: 130369013.956719 Resp: 130369013.956719 Degree: 1
>
> Cost_io: 128702412.000000 *Cost_cpu: 66176832513970*
>
> Resp_io: 128702412.000000 Resp_cpu: 66176832513970
>

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