Stefan,
The same bad estimate appears on 12.1.0.2 - I'm a little surprised that you got
the "good" behaviour you did from 11.2.0.3 - the optimizer has been able to use
the index NDV like a column group since at least 11.1.0.7.
I think I've written this one up somewhere - but can't find it. The problem is
that when you have a column group that can be used Oracle seems to "forget" to
take note of the num_nulls of the individual columns and doesn't factor them in
to the calculation.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Stefan Koehler [contact@xxxxxxxx]
Sent: 06 April 2016 09:00
To: Petr.Novak@xxxxxxxxxxxx; oracle-l
Subject: Re: Optimizer question
Hello Petr,
just rebuild a short model of your issue on 11.2.0.3.6 and 12.1.0.1 as i got no
11.2.0.4.
11.2.0.3.6
-------------------------------
Table Stats::
Table: T Alias: T
#Rows: 100000 #Blks: 244 AvgRowLen: 3.00 ChainCnt: 0.00
Index Stats::
Index: T_I Col#: 1 2
LVLS: 1 #LB: 210 #DK: 10 LB/K: 21.00 DB/K: 16.00 CLUF: 161.00 <<<
The same
…
Single Table Cardinality Estimation for T[T]
Column (#1): A(
AvgLen: 22 NDV: 0 Nulls: 100000 Density: 0.000000 Min: 0 Max: 0
Column (#2): B(
AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 1 Max: 10
ColGroup (#1, Index) T_I
Col#: 1 2 CorStregth: 0.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.0000
<<< ColGroup #1 used and "correct" cardinality
Table: T Alias: T
Card: Original: 100000.000000 Rounded: 1 Computed: 0.00 Non Adjusted:
0.00
12.1.0.1
-------------------------------
Table Stats::
Table: T Alias: T
#Rows: 100000 #Blks: 244 AvgRowLen: 3.00 ChainCnt: 0.00
Index Stats::
Index: T_I Col#: 1 2
LVLS: 1 #LB: 210 #DK: 10 LB/K: 21.00 DB/K: 16.00 CLUF: 161.00
<<< The same
…
Single Table Cardinality Estimation for T[T]
Column (#1): A(NUMBER)
AvgLen: 22 NDV: 0 Nulls: 100000 Density: 0.000000
Column (#2): B(NUMBER)
AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 1.000000 Max: 10.000000
ColGroup (#1, Index) T_I
Col#: 1 2 CorStregth: 0.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.1000
<<< ColGroup #1 used but "wrong" cardinality
Table: T Alias: T
Card: Original: 100000.000000 Rounded: 10000 Computed: 10000.00 Non
Adjusted: 10000.00
+10.2.0.4 (if i remember correctly) makes use of composite indexes for such
estimates if possible. In addition column group stats and histograms on
the equality predicates that match the index columns may over-rule the index
stats.
However based on my short re-model it looks like bug #20486828 which seems to
affect >= 11.2.0.3.12, but not 12.1.0.2 (unfortunately i have no
12.1.0.2 on my mobile lab right now). This would also fit to my working
11.2.0.3.6 case.
12.1.0.1 with "_optimizer_extended_stats_usage_control = 254"
-------------------------------
Table Stats::
Table: T Alias: T
#Rows: 100000 #Blks: 244 AvgRowLen: 3.00 ChainCnt: 0.00
Index Stats::
Index: T_I Col#: 1 2
LVLS: 1 #LB: 210 #DK: 10 LB/K: 21.00 DB/K: 16.00 CLUF: 161.00
…
Single Table Cardinality Estimation for T[T]
Column (#1): A(NUMBER)
AvgLen: 22 NDV: 0 Nulls: 100000 Density: 0.000000
Column (#2): B(NUMBER)
AvgLen: 3 NDV: 10 Nulls: 0 Density: 0.100000 Min: 1.000000 Max: 10.000000
Table: T Alias: T
Card: Original: 100000.000000 Rounded: 1 Computed: 0.00 Non Adjusted:
0.00 <<< "Correct" cardinality again without col groups
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Petr Novak <Petr.Novak@xxxxxxxxxxxx> hat am 6. April 2016 um 05:36--
geschrieben:
Hallo,
query on 11.2.0.4 DB
Table T has 1000000 rows, column A has all values Null, column B has 10
different values , no Nulls.
Index on T(A,B) is defined, num_distinct for index is 10.
select * from T where A=? , where ? is not Null
uses column statistics, expects 1 row, makes index scan
select * from T where A=? and B=? , both not Null uses index
statistics, expects 100000 rows, makes table full scan.
Why for the second query optimizer switched from using column statistics to
index statistics ? It is some bug ? How to get correct plan for the
second query ?
Best Regards,
Petr
