OK, here's the full example now I've had time to come back to it
SQL> create table TAB_TEST1 ( GUID number, stat varchar2(1) );
Table created.
SQL>
SQL> create sequence sq_test1 cache 1000;
Sequence created.
SQL>
SQL> INSERT /*+ APPEND */ INTO TAB_TEST1 select sq_test1.NEXTVAL, 'Y' from dual connect by level <= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> INSERT /*+ APPEND */ INTO TAB_TEST1 select sq_test1.NEXTVAL, 'N' from dual connect by level <= 1500000;
1500000 rows created.
SQL> CREATE INDEX IX_STAT ON TAB_TEST1 (STAT);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','TAB_TEST1');
PL/SQL procedure successfully completed.
SQL>
SQL> select histogram from user_tab_col_statistics
2 where table_name = 'TAB_TEST1'
3 and column_name = 'STAT';
HISTOGRAM
---------------
NONE
1 row selected.
SQL>
SQL> select count(*)
2 from user_tab_histograms
3 where table_name = 'TAB_TEST1'
4 and column_name = 'STAT';
COUNT(*)
----------
2
1 row selected.
--
-- You can see that at this point, we have NO histogram
--
SQL> select * from TAB_TEST1 where stat = 'Z';
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID b0vapgn674m4q, child number 0
-------------------------------------
select * from TAB_TEST1 where stat = 'Z'
Plan hash value: 1414169785
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1220 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_TEST1 | 1136K| 8877K| 1220 (2)| 00:00:15 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STAT"='Z')
18 rows selected.
SQL> select * from TAB_TEST1 where stat = 'V';
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5v9aqvvxv16yf, child number 0
-------------------------------------
select * from TAB_TEST1 where stat = 'V'
Plan hash value: 1414169785
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1220 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_TEST1 | 1250K| 9765K| 1220 (2)| 00:00:15 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STAT"='V')
18 rows selected.
SQL> select * from TAB_TEST1 where stat = 'M';
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 1sf991p8y0s87, child number 0
-------------------------------------
select * from TAB_TEST1 where stat = 'M'
Plan hash value: 1414169785
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1220 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_TEST1 | 1136K| 8877K| 1220 (2)| 00:00:15 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STAT"='M')
18 rows selected.
SQL> select * from TAB_TEST1 where stat = 'G';
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 4apjjdcbrsydq, child number 0
-------------------------------------
select * from TAB_TEST1 where stat = 'G'
Plan hash value: 1414169785
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1220 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_TEST1 | 454K| 3551K| 1220 (2)| 00:00:15 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STAT"='G')
18 rows selected.
SQL> select * from TAB_TEST1 where stat = 'B';
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5272w7agrdzxj, child number 0
-------------------------------------
select * from TAB_TEST1 where stat = 'B'
Plan hash value: 459980392
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_TEST1 | 1 | 8 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_STAT | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STAT"='B')
19 rows selected.
--
-- And from above you can see the "boundary value" stuff in effect, ie,
-- the further away we get, the lower the estimate
--
-- Now we insert a new value, not that this really matters, it is the queries above
-- that have told the database we have an interest in the STAT column
--
SQL> INSERT INTO TAB_TEST1 VALUES (sq_test1.NEXTVAL, 'H');
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','TAB_TEST1');
PL/SQL procedure successfully completed.
SQL>
SQL> select histogram from user_tab_col_statistics
2 where table_name = 'TAB_TEST1'
3 and column_name = 'STAT';
HISTOGRAM
---------------
FREQUENCY
1 row selected.
SQL>
SQL> select count(*)
2 from user_tab_histograms
3 where table_name = 'TAB_TEST1'
4 and column_name = 'STAT';
COUNT(*)
----------
3
1 row selected.
--
-- See, now we have a histogram, so we get *different* calculations on values
--
SQL>
SQL> select * from TAB_TEST1 where stat = 'Z';
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID b0vapgn674m4q, child number 0
-------------------------------------
select * from TAB_TEST1 where stat = 'Z'
Plan hash value: 1414169785
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1220 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_TEST1 | 1136K| 8877K| 1220 (2)| 00:00:15 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STAT"='Z')
18 rows selected.
SQL> DELETE FROM TAB_TEST1 WHERE STAT = 'H';
1 row deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','TAB_TEST1');
PL/SQL procedure successfully completed.
SQL>
SQL> select histogram from user_tab_col_statistics
2 where table_name = 'TAB_TEST1'
3 and column_name = 'STAT';
HISTOGRAM
---------------
FREQUENCY
1 row selected.
SQL>
SQL> select count(*)
2 from user_tab_histograms
3 where table_name = 'TAB_TEST1'
4 and column_name = 'STAT';
COUNT(*)
----------
2
1 row selected.
SQL>
SQL> select * from TAB_TEST1 where stat = 'Z';
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID b0vapgn674m4q, child number 0
-------------------------------------
select * from TAB_TEST1 where stat = 'Z'
Plan hash value: 1414169785
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1220 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_TEST1 | 1136K| 8877K| 1220 (2)| 00:00:15 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STAT"='Z')
18 rows selected.
SQL>
and even though we deleted H, we will keep that FREQUENCY histogram each time we gather stats. Hope this helps