Re: Optimizer chooses wring index?

Thank you Toon,

you are right. Request_ID is NOT defined as NOT NULL - so it's null-able. Issue solved.

Martin

2017-11-07 14:27 GMT+01:00 Toon Koppelaars <toon.koppelaars_at_rulegen.com>:

> 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