Re: partitions cardinality

Thanks for the explanation. The out of range was the trick!

Thanks again

On Tue, Apr 21, 2015 at 4:17 PM, Mauro Pagano <mauro.pagano_at_gmail.com> wrote:

> I think the partition adjustments isn't the main factor here, if you
> recreate the table as non partitioned you get the same estimation (more or
> less).
>
> The main reason for the drop I think is because part of the requested
> interval is out-of-range
> - Filter is i_date >= to_date('20150402', 'yyyymmdd') and i_date <
> to_date('20150515', 'yyyymmdd')
> - Depending when you create the TC you have a low value around 2015/04/18
> or 2015/04/21 (not so great to work with a TC that changes overnight though
> :-()
>
> So the overlapping interval would roughly be 20150421->20150515 (upper
> bound excluded so it should be +1*1/NDV) that translates to a selectivity
> of aprox 0.405 that leads to around 81k
> There are probably some more small adjustments here and there to change
> the numbers a little more.
>
> Corrections are super-welcome, as usual :-)
>
>
>
>
> On Tue, Apr 21, 2015 at 4:25 AM, Stefan Koehler <contact_at_soocs.de> wrote:
>
>> Hi Cheng,
>> just a few points to mention about your provided test case. Here is a CBO
>> trace from my environment.
>>
>> ***************************************
>> BASE STATISTICAL INFORMATION
>> ***********************
>> Table Stats::
>> Table: F_ORDER Alias: F_ORDER (Using composite stats)
>> (making adjustments for partition skews)
>> ORIGINAL VALUES:: #Rows: 200000 #Blks: 650 AvgRowLen: 16.00
>> ChainCnt: 0.00
>> PARTITIONS::
>> PRUNED: 2
>> ANALYZED: 2 UNANALYZED: 0
>> #Rows: 200000 #Blks: 412 AvgRowLen: 16.00 ChainCnt: 0.00
>> Access path analysis for F_ORDER
>> ***************************************
>> SINGLE TABLE ACCESS PATH
>> Single Table Cardinality Estimation for F_ORDER[F_ORDER]
>>
>> Column (#1): I_DATE(
>> AvgLen: 8 NDV: 60 Nulls: 0 Density: 0.016667 Min: 2457134 Max: 2457193
>> Table: F_ORDER Alias: F_ORDER
>> Card: Original: 200000.000000 Rounded: 79936 Computed: 79935.97
>> Non Adjusted: 79935.97
>> Access Path: TableScan
>> Cost: 86.67 Resp: 86.67 Degree: 0
>> Cost_io: 86.00 Cost_cpu: 30821350
>> Resp_io: 86.00 Resp_cpu: 30821350
>> Best:: AccessPath: TableScan
>> Cost: 86.67 Degree: 1 Resp: 86.67 Card: 79935.97 Bytes: 0
>> ***************************************
>>
>> 1) The global statistics are used (trace text "Using composite stats"),
>> because of the query predicates span over multiple partitions. Local
>> statistics would be used, if the query would prune to one partition only.
>>
>> 2) The global statistic #Blks is adjusted from 650 to 412. The 412
>> results from the two local partition #Blks statistics. In my case partition
>> P_201504 (110 #Blks) + partition P_201505 (302 #Blks).
>>
>> 3) However there also seems to be some adjustment to the selectivity that
>> i am not aware of as it seems like the common arithmetic for between
>> predicates does not work out here: (high_limit - low_limit) / (high_value
>> – low_value) + 2 x 1/NDV = 43 / 59 + 2 x 0.016667 = selectivity of
>> 0.76214755932203. Cardinality = 200000 x 0.76214755932203 = 152429.511
>> rows, but Oracle got 79936 rows, which is much closer to the reality (80012
>> rows).
>>
>> Maybe Jonathan Lewis or Randolf Geist know how this is scaled down in
>> case of pruning to multiple partitions.
>>
>> Best Regards
>> Stefan Koehler
>>
>> Freelance Oracle performance consultant and researcher
>> Homepage: http://www.soocs.de
>> Twitter: _at_OracleSK
>>
>>
>> > Ls Cheng <exriscer_at_gmail.com> hat am 18. April 2015 um 18:39
>> geschrieben:
>> >
>> > select count(*) from f_order
>> > where i_date >= to_date('20150402', 'yyyymmdd')
>> > and i_date < to_date('20150515', 'yyyymmdd')
>> >
>> > ***************************************
>> > BASE STATISTICAL INFORMATION
>> > ***********************
>> > Table Stats::
>> > Table: F_ORDER Alias: F_ORDER (Using composite stats)
>> > (making adjustments for partition skews)
>> > ORIGINAL VALUES:: #Rows: 200000 #Blks: 674 AvgRowLen: 16.00
>> ChainCnt: 0.00
>> > PARTITIONS::
>> > PRUNED: 2
>> > ANALYZED: 2 UNANALYZED: 0
>> > #Rows: 200000 #Blks: 492 AvgRowLen: 16.00 ChainCnt: 0.00
>> > Access path analysis for F_ORDER
>> > ***************************************
>> > SINGLE TABLE ACCESS PATH
>> > Single Table Cardinality Estimation for F_ORDER[F_ORDER]
>> >
>> > Column (#1): I_DATE(
>> > AvgLen: 8 NDV: 60 Nulls: 0 Density: 0.016667 Min: 2457131 Max:
>> 2457190
>> > Table: F_ORDER Alias: F_ORDER
>> > Card: Original: 200000.000000 Rounded: 91382 Computed: 91382.02
>> Non Adjusted: 91382.02
>> > Access Path: TableScan
>> > Cost: 137.33 Resp: 137.33 Degree: 0
>> > Cost_io: 135.00 Cost_cpu: 36038010
>> > Resp_io: 135.00 Resp_cpu: 36038010
>> > Best:: AccessPath: TableScan
>> > Cost: 137.33 Degree: 1 Resp: 137.33 Card: 91382.02 Bytes:
>> 0
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

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