Hi All,
Oracle 11.2.0.3, 64 bit on Linux.
We use the default (out-of-the-box) statistics collections by Oracle. I have a table with 130M records, with one unique key index. I am taking a simple count of records based on a date column (DO_DATE) which is the third column in that unique key index (hence all those INDEX FAST FULL SCAN in following plans).
There are histograms on all columns in the table (indexed and non-indexed both).
My question is, why Oracle is estimating/guessing wrong number of records in 2 out of the following 4 cases ??
In Case 2, there are 1071 records, but Oracle thought there are 35074. In Case 4, there are 171K records, but Oracle thought there are 35074.
I hope, I am interpreting the numbers in the plans correctly :)
SQL> set autotrace on explain
-- ==================== CASE 1 ===================================================
SQL> select count(*) from TABLE1 where DO_DATE=to_date('14012013','DDMMYYYY');
COUNT(*)
----------
19957936
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1543387839
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 253K (2)| 00:50:43 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| TABLE1_UK | 20M| 156M| 253K (2)| 00:50:43 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DO_DATE"=TO_DATE(' 2013-01-14 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
-- ==================== CASE 2 ===================================================
SQL> select count(*) from TABLE1 where DO_DATE=to_date('03052011','DDMMYYYY');
COUNT(*)
----------
1071
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1543387839
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 253K (2)| 00:50:43 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| TABLE1_UK | 35074 | 274K| 253K (2)| 00:50:43 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DO_DATE"=TO_DATE(' 2011-05-03 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
-- ==================== CASE 3 ===================================================
SQL> select count(*) from TABLE1 where DO_DATE=to_date('16102007','DDMMYYYY');
COUNT(*)
----------
4
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1543387839
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 253K (2)| 00:50:43 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| TABLE1_UK | 1 | 8 | 253K (2)| 00:50:43 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DO_DATE"=TO_DATE(' 2007-10-16 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
-- ==================== CASE 4 ===================================================
SQL> select count(*) from TABLE1 where DO_DATE=to_date('10042012','DDMMYYYY');
COUNT(*)
----------
171897
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1543387839
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 253K (2)| 00:50:43 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| TABLE1_UK | 35074 | 274K| 253K (2)| 00:50:43 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DO_DATE"=TO_DATE(' 2012-04-10 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Thanks in advance