--创建表
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的。只能使用等高直方图了。
