I forgot to add an example, simple example btw.
create table f_order
(
i_date date,
i_segment number,
i_client number
)
partition by range (i_date)
(
partition p_201504 values less than (to_date('20150501', 'yyyymmdd')),
partition p_201505 values less than (to_date('20150601', 'yyyymmdd')),
partition p_201506 values less than (to_date('20150701',
'yyyymmdd'))
);
insert into f_order
select sysdate + mod(rownum, 60), rownum, mod(rownum, 10)
from dual
connect by level <= 200000;
exec dbms_stats.gather_table_stats('OWN_DWSPEC', 'F_ORDER', METHOD_OPT =>
'FOR ALL COLUMNS SIZE 1')
No index, no histograms, partitioning pruning, literals used
I got following in 10053 traces (for 2 queries), I cant find how the
cardinality was computed :-?
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
select count(*) from f_order
where i_date >= to_date('20150402', 'yyyymmdd')
and i_date < to_date('20150501', '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: 47314 Computed: 47314.23 Non
Adjusted: 47314.23
Access Path: TableScan
Cost: 137.22 Resp: 137.22 Degree: 0
Cost_io: 135.00 Cost_cpu: 34429601
Resp_io: 135.00 Resp_cpu: 34429601
Best:: AccessPath: TableScan
Cost: 137.22 Degree: 1 Resp: 137.22 Card: 47314.23 Bytes: 0
On Sat, Apr 18, 2015 at 4:20 PM, William Robertson <
william@xxxxxxxxxxxxxxxxxxxx> wrote:
That will depend.
Off the top of my head, if the partition can be determined from a
literal (so the query is explicitly about a specific partition) then
the partition stats will be used. If not, the optimiser will go to the
global stats for the num_rows of the table divided by the num_distinct
of the partition key column. There are doubtless a load more factors
involved though (dynamic sampling, index stats, whether you have bind
peeking disabled...) so you may need to be more specific.
William Robertson
On 18 Apr 2015, at 14:47, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi
Does anyone know how cardinality is calculated in partitioned tables?
TIA
