optimizer strange index decision

From: Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
Date: Thu, 17 Mar 2022 16:42:43 +0200
Message-ID: <CA+riqSXwYtqnFnWaSEC+ar-g0GOGVM-m3RFXrP4jtX+EPCAzNw_at_mail.gmail.com>



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))

  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
Received on Thu Mar 17 2022 - 15:42:43 CET

Original text of this message