Statistics contradiction can cause wrong cardinality estimation

Hi All,

I have two cases where the cardinality are both rounded to 1 while the actual number of records returned are big. No histograms on both case, both predicate value also within the Low and High value.

I might be wrong about below assumptions, so stand to be corrected. TIA.

1. When NUM_DISTINCT of column is 1, but the LOW_VALUE and HIGH_VALUE are different. There is contradiction as LOW_VALUE differs from HIGH_VALUE means the NUM_DISTINCT should be greater than 1.

   Query:
    select count(*) from ACC where acc_date=TO_DATE('08072008','DDMMRRRR')    

   Plan:

  ---------------------------------------+------------+--------------+                                                                  


  | Id |Operation                |Name   |Rows | Cost |Pstart |Pstop |                                                                  

  ---------------------------------------+------------+--------------+                                                                  


  | 0  |SELECT STATEMENT         |       |     | 1702 |       |      |                                                                  

  | 1  | SORT AGGREGATE          |       |   1 |      |       |      |                                                                  

  | 2  |  PARTITION RANGE SINGLE |       |   1 | 1702 | KEY   |KEY   |                                                                  

  | 3  |   PARTITION LIST ALL    |       |   1 | 1702 | 1     |3     |                                                                  

  | 4  |    INDEX FAST FULL SCAN |ACC_PK |   1 | 1702 | KEY   |KEY   |                                                                  

  ---------------------------------------+------------+--------------+                                                            

   TO_DATE('08072008','DDMMRRRR') is inside the range of LOW_VALUE and HIGH_VALUE    on both partition and global level.
   ACC is RANGE-LIST partition on (ACC_DATE, COL1).    ACC_PK is local index on column (COL1, COL2, ACC_DATE).    

   Cardinality from trace 10053:
   Column (#3): ACC_DATE(DATE)
    AvgLen: 8.00 NDV: 1 Nulls: 0 Density: 1 Min: 2454653 Max: 2454657    Table: ACC Alias: ACC
    Card: Original: 2482463 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00

2. When NUM_NULLS of column is greater than NUM_ROWS of table. There is contradiction as NUM_NULLS should be less or equal to NUM_ROWS.    

   Query:
    select count(*) from myacc where myblock = 'X'    

   Plan:

  --------------------------------------+---------------


  | Id  | Operation           | Name    | Rows  |Cost  |

  --------------------------------------+---------------


  | 0   | SELECT STATEMENT    |         |       |  446 |

  | 1   |  SORT AGGREGATE     |         |     1 |      |

  | 2   |   TABLE ACCESS FULL | MYACC   |     1 |  446 |

  --------------------------------------+---------------
   

   'X' is inside the range of LOW_VALUE and HIGH_VALUE.    

   Cardinality from trace 10053:
   Column (#3): MYBLOCK(VARCHAR2)
    AvgLen: 2.00 NDV: 1 Nulls: 1643559 Density: 1    Table: MYACC Alias: MYACC
    Card: Original: 1293716 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00

Note: as the source of problem is inaccurate stats, recompute it solved the cardinality problem.

regards,
tomi       

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