Re: Optimizer chooses wring index?

Is Request_Id null-able?

Null's are not stored in the _N1 index. They are in _BX1...

On Tue, Nov 7, 2017 at 2:19 PM, Martin Berger <martin.a.berger_at_gmail.com> wrote:

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

-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.rulegen.com/am4dp-backcover-text

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