Re: optimizer strange index decision

Hi

You could check index clustering factors.

Cheers
Jko

On March 17, 2022 15:43:01 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