--创建表

SQL> create table tab (a number, b number); 

Table created.

--插入数据

SQL> begin

  2   for i in 1..10000 loop

  3     insert into tab values (i, i);

  4   end loop;

  5   commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

--更新部分数据

SQL> update tab set b=5 where b between 6 and 9995;

9990 rows updated.

--提交

SQL> commit;

Commit complete.

--创建索引

SQL> create index ix_tab_b on tab(b);

Index created.

--数据分布

SQL> SELECT b,count(*) from tab group by b  order by 1;

         B   COUNT(*)

---------- ----------

         1          1

         2          1

         3          1

         4          1

         5       9991

      9996          1

      9997          1

      9998          1

      9999          1

     10000          1

10 rows selected.

SQL> 

--禁止产生直方图（size 1）

SQL> BEGIN

  2    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'NC50',

  3                                  TABNAME    => 'TAB',

  4                                  CASCADE    => TRUE,

  5                                  METHOD_OPT => 'FOR  COLUMNS B SIZE 1 ');

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

--查看视图USER_TAB_HISTOGRAMS

SQL> col COLUMN_NAME format a20

SQL> col TABLE_NAME format a15

SQL> SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB';

TABLE_NAME      COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE

--------------- -------------------- --------------- --------------

TAB             B                                  0              1

TAB             B                                  1          10000

SQL>

--注：ENDPOINT_NUMBER列值是bucket的标识号。现在只有0、1两个bucket说明没有产生直方图信息。

--在没有直方图的情况下，在B列上进行等值查询的时候，都是索引范围扫描（即使返回总数据量的80%）

SQL> set autotrace traceonly explain

SQL> set line 180

SQL> select * from tab where b=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 439197569

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |  1000 |  7000 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |  1000 |  7000 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |  1000 |       |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("B"=1)

SQL> 

SQL> select * from tab where b=5;---返回9991条数据，总数据量的80%

Execution Plan

----------------------------------------------------------

Plan hash value: 439197569

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |  1000 |  7000 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |  1000 |  7000 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |  1000 |       |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("B"=5)

SQL> 

--收集直方图信息。看看是什么效果。由于列B唯一值的个数没有超过254因此产生的是等频直方图。

SQL> BEGIN

  2    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'NC50',

  3                                  TABNAME    => 'TAB',

  4                                  CASCADE    => TRUE,

  5                                  METHOD_OPT => 'FOR  COLUMNS B SIZE AUTO ');

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL> 

--在B=1时候采用索引扫描

SQL> set autotrace traceonly explain

SQL> select * from tab where b=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 439197569

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |     7 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB      |     1 |     7 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("B"=1)

--在B=5时候，已经采用全表扫描了，说明直方图起了作用

SQL> select * from tab where b=5;

Execution Plan

----------------------------------------------------------

Plan hash value: 1995730731

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  9991 | 69937 |     9   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TAB  |  9991 | 69937 |     9   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("B"=5)

SQL>

--查看此时的直方图信息：

SQL> set autotrace off;

SQL> col TABLE_NAME format a10

SQL> col COLUMN_NAME format a20

SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE

  2  FROM USER_TAB_HISTOGRAMS

  3  WHERE TABLE_NAME = 'TAB';

TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE

---------- -------------------- --------------- --------------

TAB        B                                  1              1

TAB        B                                  2              2

TAB        B                                  3              3

TAB        B                                  4              4

TAB        B                               9995              5

TAB        B                               9996           9996

TAB        B                               9997           9997

TAB        B                               9998           9998

TAB        B                               9999           9999

TAB        B                              10000          10000

10 rows selected.

SQL> 

--其中EDNPOINT_NUMBER是累计值，EDNPOINT_NUMBER(N)-EDNPOINT_NUMBER(N-1)=当前桶的数据个数=9995-4=9991

--EDNPOINT_VALUE是列的值。

--在看看等高直方图的情况，由于列B有10个唯一值，通过size 8可以强制ORACLE使用等高直方图

SQL> BEGIN

  2    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'NC50',

  3                                  TABNAME    => 'TAB',

  4                                  CASCADE    => TRUE,

  5                                  METHOD_OPT => 'FOR  COLUMNS B SIZE 8 ');

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS

  2  WHERE TABLE_NAME = 'TAB' ;

WHERE TABLE_NAME = 'TAB'

                        *

ERROR at line 2:

ORA-00933: SQL command not properly ended

SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE

  2  FROM USER_TAB_HISTOGRAMS

  3  WHERE TABLE_NAME = 'TAB';

TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE

---------- -------------------- --------------- --------------

TAB        B                                  0              1

TAB        B                                  7              5

TAB        B                                  8          10000

SQL>

--从查询结果惊奇的发现只有三个桶0 7 8

--说明：在等高直方图中，EDNPOINT_NUMBER代表桶号,这一点与等频直方图不同。

SQL> set autotrace traceonly explain;

SQL> select * from tab where b=5;

Execution Plan

----------------------------------------------------------

Plan hash value: 1995730731

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  8750 | 61250 |     9   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TAB  |  8750 | 61250 |     9   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("B"=5)

SQL> 

--发现执行计划的ROWS部分，ORACLE计算出来的cardinality不是特别精确的。9991才是精确值。而等频直方图可以精确到9991，因此可以说等频直方图比等高直方图稳定，精确。可是现实很多时候，列的唯一值是超过254的。只能使用等高直方图了。