Hello!
I have a problem with extended statistics on 11.2.0.3
Here is the script I run
drop table col_stats;
create table col_stats as
select 1 a, 2 b,
from dual
connect by level<=100000;
insert into col_stats (
select 2, 1,
from dual
connect by level<=100000);
-- check the a,b distribution
-- extended stats DEFINITION
select dbms_stats.create_extended_stats('A','COL_STATS','(A,B)') name
from dual;
-- set estimate_percent to 100%
EXEC dbms_stats.SET_TABLE_prefs ('A','COL_STATS','ESTIMATE_PERCENT',100);
-- check the changes
select dbms_stats.get_prefs ('ESTIMATE_PERCENT','A','COL_STATS')
from dual;
-- NOW GATHER COLUMN STATS
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => 'A',
TABNAME => 'COL_STATS',
METHOD_OPT => 'FOR ALL COLUMNS' );
END;
/
set autotrace traceonly explain
select * from col_stats where a=1 and b=1;
SQL> select * from col_stats where a=1 and b=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1829175627
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 683K| 177 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| COL_STATS | 50000 | 683K| 177 (2)| 00:00:03 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1 AND "B"=1)
How come the optimizer expects 50000 rows?
Thanks in advance.
Rob