Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Extended statistics issue

RobKJan 8 2014 — edited Jan 10 2014

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

A    BCOUNT(1)

2    1  100000
1    2  100000

-- 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

This post has been answered by Martin Preiss on Jan 8 2014
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 7 2014
Added on Jan 8 2014
11 comments
3,133 views