Re: optimizer strange index decision

I would drop one of them.
I don't see any reason to support both of them.

PS. Note that statistics show lower number of distinct keys for wrong_index.

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE
http://orasql.org

чт, 17 мар. 2022 г., 17:47 Google <jacques.kostic_at_gmail.com>:

> 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