Hint "column_stats" to disable histograms

output.sql

SQL> create table test(a,b) as
  2  select 1, 1 from dual
  3  union all
  4  select 10, 10 from dual connect by level<=10
  5  union all
  6  select 100, rownum from dual connect by level<=100
  7  union all
  8  select 1000, rownum from dual connect by level<=1000
  9  union all
 10  select 1e6, 0 from dual;

Table created.

SQL> call dbms_stats.gather_table_stats('','test',estimate_percent=>100, method_opt=>'for all columns size 255');

Call completed.

SQL> @stats/histograms test a
Histograms by owner/table/column_name
Syntax 1: @histograms owner table column
Syntax 2: @histograms table column

OWNER                     TABLE_NAME                     COLUMN_NAME                    DATA_TYPE                 EP_VALUE                                ENDPOINT_VALUE DELTA_VALUES ENDPOINT_NUMBER DELTA_NUMBERS ENDPOINT_ACTUAL_VALUE
------------------------- ------------------------------ ------------------------------ ------------------------- --------------------------------------- -------------- ------------ --------------- ------------- --------------------------------------------------
XTENDER                   TEST                           A                              NUMBER                    1                                                    1            1               1             1 1
XTENDER                   TEST                           A                              NUMBER                    10                                                  10            9              11            10 10
XTENDER                   TEST                           A                              NUMBER                    100                                                100           90             111           100 100
XTENDER                   TEST                           A                              NUMBER                    1000                                              1000          900            1111          1000 1000
XTENDER                   TEST                           A                              NUMBER                    1000000                                        1000000       999000            1112             1 1000000

SQL> explain plan for select * from test where a=10;

Explained.

SQL> @xplan

P_FORMAT
----------------
typical


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    70 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |    10 |    70 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=10)

13 rows selected.

SQL> explain plan for select * from test where a=:10;

Explained.

SQL> @xplan

P_FORMAT
----------------
typical


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   222 |  1554 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |   222 |  1554 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=TO_NUMBER(:10))

13 rows selected.

SQL> explain plan for select/*+ column_stats("XTENDER"."TEST", "A", scale, length=3 distinct=5 nulls=0 min=1 max=1000000) */ * from test where a=10;

Explained.

SQL> @xplan +HINT_REPORT

P_FORMAT
----------------------------
typical +HINT_REPORT


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   222 |  1554 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |   222 |  1554 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=10)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   0 -  STATEMENT
           -  column_stats("XTENDER"."TEST", "A", scale, length=3 distinct=5 nulls=0 min=1 max=1000000)

20 rows selected.