The collection histogram of dbms_stats.gather_table_stats under Oracle11.2.0.1.0 is incorrect.

SQL> select * from v $ version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production


SQL> -- create some data
SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> update test set object_id = 2;
SQL> update test set object_id = 1 where rownum = 1;
SQL> commit;
SQL> create index ind_t_object_id on test (object_id );
SQL> exec dbms_stats.gather_table_stats (user, 'test', cascade => true );
SQL> -- view the data distribution
SQL> select object_id, count (1) from test group by object_id;
OBJECT_ID COUNT (1)
--------------------
1 1
2 72415

SQL> set autotrace traceonly
SQL> -- the index should be used.
SQL> select * from test where object_id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 36208 | 3359K | 290 (1) | 00:00:04 |
| * 1 | table access full | TEST | 36208 | 3359K | 290 (1) | 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("OBJECT_ID" = 1)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
1039 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> -- full table scan is required.
SQL> select * from test where object_id = 2;
Row 72415 has been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 36208 | 3359K | 290 (1) | 00:00:04 |
| * 1 | table access full | TEST | 36208 | 3359K | 290 (1) | 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("OBJECT_ID" = 2)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
5799 consistent gets
0 physical reads
0 redo size
2940934 bytes sent via SQL * Net to client
53435 bytes encoded ed via SQL * Net from client
4829 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72415 rows processed

SQL> set autotrace off
SQL> col TABLE_NAME format a10;
SQL> col COLUMN_NAME format a10;
SQL> col ENDPOINT_ACTUAL_VALUE format a10;
SQL> col ENDPOINT_NUMBER format 9999999;
SQL> col ENDPOINT_VALUE format 999999;
SQL> -- if the histogram has a problem, re-collect the histogram.
SQL> select * from user_tab_histograms s where s. table_name = 'test' and column_name = 'object _ id ';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
-----------------------------------------------------------
TEST OBJECT_ID 0 1
TEST OBJECT_ID 1 2
SQL> exec dbms_stats.gather_table_stats (user, 'test', cascade => true, method_opt => 'for columns object_id size 2 ');

SQL> set autotrace traceonly
SQL> -- still incorrect
SQL> select * from test where object_id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 36208 | 3359K | 290 (1) | 00:00:04 |
| * 1 | table access full | TEST | 36208 | 3359K | 290 (1) | 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("OBJECT_ID" = 1)
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
1039 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> set autotrace off
SQL> select * from user_tab_histograms s where s. table_name = 'test' and column_name = 'object _ id ';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
-----------------------------------------------------------
TEST OBJECT_ID 5391 2


SQL> -- only use analyze to collect histograms
SQL> analyze table test compute statistics for table for columns object_id size 2;

SQL> select * from user_tab_histograms s where s. table_name = 'test' and column_name = 'object _ id ';
TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
-----------------------------------------------------------
TEST OBJECT_ID 1 1
TEST OBJECT_ID 72416 2

SQL> set autotrace traceonly
SQL> select * from test where object_id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 255872589
Bytes -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 99 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | TEST | 1 | 99 | 2 (0) | 00:00:01 |
| * 2 | index range scan | IND_T_OBJECT_ID | 1 | 1 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_ID" = 1)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1191 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> select * from test where object_id = 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 72415 | 7001K | 290 (1) | 00:00:04 |
| * 1 | table access full | TEST | 72415 | 7001K | 290 (1) | 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("OBJECT_ID" = 2)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
5799 consistent gets
0 physical reads
0 redo size
2940934 bytes sent via SQL * Net to client
53435 bytes encoded ed via SQL * Net from client
4829 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72415 rows processed
SQL> set autotrace off