Hi,
When I query a partitioned table, filtering by the partition key (date column) to one partition, cbo chooses full scan which which performs well.
When I query the same partitioned table, filtering by the partition key (date column) to two partitions, cbo chooses index access which which performs not so well.
¿Could you help me to know the reason?
I paste the info I think you would need.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SELECT COUNT(COLUMN_X) FROM TABLE_XXX
where DATE_COLUMN_PARTITION_KEY >= to_date('20130516 00:01', 'yyyymmdd hh24:mi')
AND DATE_COLUMN_PARTITION_KEY < to_date('20130517 00:01', 'yyyymmdd hh24:mi')
Plan hash value: 3669376621
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3380 (100)| | | 1 |00:00:02.35 | 9175 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | 1 |00:00:02.35 | 9175 |
| 2 | PARTITION RANGE SINGLE| | 1 | 1778K| 3380 (2)| 867 | 867 | 1840K|00:00:01.96 | 9175 |
|* 3 | TABLE ACCESS FULL | TABLE_XXX | 1 | 1778K| 3380 (2)| 867 | 867 | 1840K|00:00:01.24 | 9175 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("DATE_COLUMN_PARTITION_KEY">=TO_DATE(' 2013-05-16 00:01:00', 'syyyy-mm-dd hh24:mi:ss') AND "DATE_COLUMN_PARTITION_KEY"<TO_DATE('
2013-05-17 00:01:00', 'syyyy-mm-dd hh24:mi:ss')))
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TABLE_XXX Alias: TABLE_XXX Partition [866]
#Rows: 1778989 #Blks: 8856 AvgRowLen: 71.00 ChainCnt: 0.00
#Rows: 1778989 #Blks: 8856 AvgRowLen: 71.00 ChainCnt: 0.00
Index Stats::
Index: TABLE_XXX_I Col#: 9 PARTITION [866]
LVLS: 2 #LB: 2130 #DK: 1477 LB/K: 1.00 DB/K: 6.00 CLUF: 9523.00
LVLS: 2 #LB: 2130 #DK: 1477 LB/K: 1.00 DB/K: 6.00 CLUF: 9523.00
Index: TABLE_XXX_I2 Col#: 2 PARTITION [866]
LVLS: 2 #LB: 3411 #DK: 10198 LB/K: 1.00 DB/K: 14.00 CLUF: 143158.00
LVLS: 2 #LB: 3411 #DK: 10198 LB/K: 1.00 DB/K: 14.00 CLUF: 143158.00
Index: TABLE_XXX_PK Col#: 5 4 1 2 3 8 6 9 PARTITION [866]
LVLS: 2 #LB: 8986 #DK: 1794316 LB/K: 1.00 DB/K: 1.00 CLUF: 1489295.00
LVLS: 2 #LB: 8986 #DK: 1794316 LB/K: 1.00 DB/K: 1.00 CLUF: 1489295.00
Access path analysis for TABLE_XXX
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TABLE_XXX[TABLE_XXX]
Column (#5):
NewDensity:0.000048, OldDensity:0.000093 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:21048
Column (#5):
NewDensity:0.019119, OldDensity:0.000000 BktCnt:6564, PopBktCnt:6564, PopValCnt:24, NDV:24
Column (#5): DATE_COLUMN_PARTITION_KEY( Part#: 866
AvgLen: 8 NDV: 24 Nulls: 0 Density: 0.019119 Min: 2456429 Max: 2456430
Histogram: Freq #Bkts: 24 UncompBkts: 6564 EndPtVals: 24
Column (#5): DATE_COLUMN_PARTITION_KEY(
AvgLen: 8 NDV: 24 Nulls: 0 Density: 0.019119 Min: 2456429 Max: 2456430
Histogram: Freq #Bkts: 24 UncompBkts: 6564 EndPtVals: 24
Table: TABLE_XXX Alias: TABLE_XXX
Card: Original: 1778989.000000 Rounded: 1778989 Computed: 1778989.00 Non Adjusted: 1778989.00
Access Path: TableScan
Cost: 3379.84 Resp: 3379.84 Degree: 0
Cost_io: 3322.00 Cost_cpu: 1242625795
Resp_io: 3322.00 Resp_cpu: 1242625795
Access Path: index (RangeScan)
Index: TABLE_XXX_PK
resc_io: 1498283.00 resc_cpu: 8135133505
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1498661.64 Resp: 1498661.64 Degree: 1
Best:: AccessPath: TableScan
Cost: 3379.84 Degree: 1 Resp: 3379.84 Card: 1778989.00 Bytes: 0
SELECT COUNT(COLUMN_X) FROM TABLE_XXX
where DATE_COLUMN_PARTITION_KEY >= to_date('20130516 00:01', 'yyyymmdd hh24:mi')
AND DATE_COLUMN_PARTITION_KEY < to_date('20130518 00:01', 'yyyymmdd hh24:mi')
Plan hash value: 4270454524
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 409 (100)| | | 1 |00:00:19.98 | 3068K| 9 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | 1 |00:00:19.98 | 3068K| 9 |
| 2 | PARTITION RANGE ITERATOR | | 1 | 74439 | 409 (0)| 867 | 868 | 3671K|00:00:19.21 | 3068K| 9 |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE_XXX | 2 | 74439 | 409 (0)| 867 | 868 | 3671K|00:00:17.94 | 3068K| 9 |
|* 4 | INDEX RANGE SCAN | TABLE_XXX_PK | 2 | 1 | 5 (0)| 867 | 868 | 3671K|00:00:02.90 | 18348 | 0 |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DATE_COLUMN_PARTITION_KEY">=TO_DATE(' 2013-05-16 00:01:00', 'syyyy-mm-dd hh24:mi:ss') AND "DATE_COLUMN_PARTITION_KEY"<TO_DATE(' 2013-05-18 00:01:00',
'syyyy-mm-dd hh24:mi:ss'))
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TABLE_XXX Alias: TABLE_XXX (Using composite stats)
(making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 1566782892 #Blks: 7804691 AvgRowLen: 72.00 ChainCnt: 0.00
PARTITIONS::
PRUNED: 2
ANALYZED: 2 UNANALYZED: 0
#Rows: 1566782892 #Blks: 17628 AvgRowLen: 72.00 ChainCnt: 0.00
Index Stats::
Index: TABLE_XXX_I Col#: 9
USING COMPOSITE STATS
LVLS: 2 #LB: 1891749 #DK: 1522688 LB/K: 1.00 DB/K: 7.00 CLUF: 10907487.00
Index: TABLE_XXX_I2 Col#: 2
USING COMPOSITE STATS
LVLS: 2 #LB: 2864613 #DK: 11097 LB/K: 258.00 DB/K: 10858.00 CLUF: 120495362.00
Index: TABLE_XXX_PK Col#: 5 4 1 2 3 8 6 9
USING COMPOSITE STATS
LVLS: 2 #LB: 7829199 #DK: 1561894453 LB/K: 1.00 DB/K: 1.00 CLUF: 1323994656.00
Access path analysis for TABLE_XXX
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TABLE_XXX[TABLE_XXX]
Column (#5):
NewDensity:0.000048, OldDensity:0.000093 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:21048
Column (#5): DATE_COLUMN_PARTITION_KEY(
AvgLen: 8 NDV: 21048 Nulls: 0 Density: 0.000048 Min: 2455563 Max: 2482256
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Table: TABLE_XXX Alias: TABLE_XXX
Card: Original: 1566782892.000000 Rounded: 74439 Computed: 74438.56 Non Adjusted: 74438.56
Access Path: TableScan
Cost: 6667.25 Resp: 6667.25 Degree: 0
Cost_io: 6612.00 Cost_cpu: 1187128776
Resp_io: 6612.00 Resp_cpu: 1187128776
Access Path: index (RangeScan)
Index: TABLE_XXX_PK
resc_io: 409.00 resc_cpu: 4341838
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 409.20 Resp: 409.20 Degree: 1
Best:: AccessPath: IndexRange
Index: TABLE_XXX_PK
Cost: 409.20 Degree: 1 Resp: 409.20 Card: 74438.56 Bytes: 0
Thanks