Optimizer chooses wring index?

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Tue, 7 Nov 2017 14:19:29 +0100
Message-ID: <CALH8A932Qmx-wqw+u6UUzzgOZV0r4Doqd1wmLVuKHjFhZivQug_at_mail.gmail.com>

Dear List,

I am facing an interesting situation:
I have a table (H3G_IT_WEBADI_LATE_RECONC) with one column of interest: Request_ID
(Number).
on this table there are 2 index:
H3G_IT_WEBADI_LATE_RECONC_N1 ON H3G_IT_WEBADI_LATE_RECONC (REQUEST_ID) and
H3G_IT_WEBADI_LATE_RECONC_*BX1* ON H3G_IT_WEBADI_LATE_RECONC (REQUEST_ID*,1* )

The query is
select /*+ OPT_PARAM('_FAST_FULL_SCAN_ENABLED' 'TRUE') */ distinct Request_id /* PARSE11 */
from H3G_OMIF.H3G_IT_WEBADI_LATE_RECONC tx; and the optimizer creates

--------------------------------------------------------------+-----------------------------------+


| Id  | Operation              | Name                         | Rows  |


Bytes | Cost  | Time      |
--------------------------------------------------------------+-----------------------------------+


| 0   | SELECT STATEMENT       |                              |       |


   |  8114 |           |


| 1   |  SORT UNIQUE           |                              |  1249 |

8743 | 8114 | 00:02:41 |
| 2 | INDEX FAST FULL SCAN | H3G_IT_WEBADI_LATE_RECONC_*BX1*| 16M |
109M | 7252 | 00:02:30 |

--------------------------------------------------------------+-----------------------------------+

The optimizers decission:
BASE STATISTICAL INFORMATION

---

Table Stats::
  Table: H3G_IT_WEBADI_LATE_RECONC Alias: TX     #Rows: 16259006 #Blks: 895397 AvgRowLen: 380.00 ChainCnt: 0.00 Index Stats::
  Index: H3G_IT_WEBADI_LATE_RECONC_*BX1* Col#: 27 30     LVLS: 2 *#LB: 47672* #DK: 1249 LB/K: 38.00 DB/K: 716.00 CLUF: 894409.00
  Index: H3G_IT_WEBADI_LATE_RECONC_*N1* Col#: 27     LVLS: 2 *#LB: 40844* #DK: 1249 LB/K: 32.00 DB/K: 716.00 CLUF: 894409.00
  Column (#27): REQUEST_ID(
    AvgLen: 7 NDV: 1249 Nulls: 0 Density: 0.000801 Min: 141993649 Max: 176687626
Access path analysis for H3G_IT_WEBADI_LATE_RECONC

---

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for H3G_IT_WEBADI_LATE_RECONC[TX]   Table: H3G_IT_WEBADI_LATE_RECONC Alias: TX     Card: Original: 16259006.000000 Rounded: 16259006 Computed: 16259006.00 Non Adjusted: 16259006.00
  Access Path: TableScan
    Cost: 134924.05 Resp: 134924.05 Degree: 0

      Cost_io: 134077.00  Cost_cpu: 17270050032
      Resp_io: 134077.00  Resp_cpu: 17270050032
  Access Path: index (index (FFS))

    Index: H3G_IT_WEBADI_LATE_RECONC_BX1     resc_io: 7140.00 resc_cpu: 2290574008     ix_sel: 0.000000 ix_sel_with_filters: 1.000000   Access Path: index (FFS)
    Cost: 7252.35 Resp: 7252.35 Degree: 1

      Cost_io: 7140.00  Cost_cpu: 2290574008
      Resp_io: 7140.00  Resp_cpu: 2290574008
  Access Path: index (FullScan)

    Index: H3G_IT_WEBADI_LATE_RECONC_BX1     resc_io: 47674.00 resc_cpu: 3591308731     ix_sel: 1.000000 ix_sel_with_filters: 1.000000     Cost: 47850.14 Resp: 47850.14 Degree: 1 ******** Begin index join costing ********

• trying bitmap/domain indexes ****** Access Path: index (FullScan) Index: H3G_IT_WEBADI_LATE_RECONC_BX1 resc_io: 47674.00 resc_cpu: 3591308731 ix_sel: 1.000000 ix_sel_with_filters: 1.000000 Cost: 47850.14 Resp: 47850.14 Degree: 0 Access Path: index (FullScan) Index: H3G_IT_WEBADI_LATE_RECONC_BX1 resc_io: 47674.00 resc_cpu: 3591308731 ix_sel: 1.000000 ix_sel_with_filters: 1.000000 Cost: 47850.14 Resp: 47850.14 Degree: 0 Bitmap nodes: Used H3G_IT_WEBADI_LATE_RECONC_BX1 Cost = 59812.680460, sel = 1.000000
• finished trying bitmap/domain indexes ****** ******** End index join costing ******** Best:: AccessPath: IndexFFS Index: H3G_IT_WEBADI_LATE_RECONC_*BX1* Cost: 7252.35 Degree: 1 Resp: 7252.35 Card: 16259006.00 Bytes: 0

(I can provide the full 10053 trace if required).

Any idea why the optimizer refuses to use index _*N1* ?

Martin

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

Received on Tue Nov 07 2017 - 14:19:29 CET