Re: partitions cardinality

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 21 Apr 2015 10:25:31 +0200 (CEST)
Message-ID: <2045617206.146332.1429604731745.JavaMail.open-xchange_at_app01.ox.hosteurope.de>

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

Received on Tue Apr 21 2015 - 10:25:31 CEST