SQL>
SQL> begin
2 for i in 1..10000 loop
3 insert into hist values (i, i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> update hist set b=5 where b between 6 and 9995;
已更新9990行。
SQL> commit;
提交完成。
SQL> create index i_hist_b on tab(b);
索引已创建。
然后分析表，强制使列B不产生直方图。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => 'HIST',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 1 ');
END;
查看视图USER_TAB_HISTOGRAMS，列B上只有最大值，最小值两条记录分别对应端点号（endpoint_number）0和1，这种显示说明列B没有直方
图信息。
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
------------------------------ ---------------------------------------- --------------- --------------
HIST B 0 1
HIST B 1 10000
在没有直方图的情况下，在B列上进行等值查询的时候，都是索引范围扫描。
-----------------------------------------------------------------
SQL> select * from hist where b =1;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1911084455
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HIST | 1000 | 6000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_HIST_B | 1000 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
570 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from hist where b =5;
9991 rows selected.
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 1911084455
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HIST | 1000 | 6000 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_HIST_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1369 consistent gets
0 physical reads
0 redo size
212165 bytes sent via SQL*Net to client
7818 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
-----生成直方图 再次收集统计信息时 务必清除之前的统计信息。否则执行计划会利用原来的统计信息
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => user,
3 TABNAME => 'HIST',
4 CASCADE => TRUE,
5 METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO ');
6 END;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.28
SQL> select * from hist where b=1;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1911084455
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HIST | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_HIST_B | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=1)
Statistics
----------------------------------------------------------
150 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
570 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from hist where b =5;
9991 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 1745918543
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 59946 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| HIST | 9991 | 59946 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
688 consistent gets
0 physical reads
0 redo size
212165 bytes sent via SQL*Net to client
7818 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
-------------------------------------------
SQL> SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
2 WHERE TABLE_NAME = 'HIST';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
----------- ------------ -------------- -------------- ---
HIST B 1 1
HIST B 2 2
HIST B 3 3
HIST B 4 4
HIST B 9995 5
HIST B 9996 9996
HIST B 9997 9997
HIST B 9998 9998
HIST B 9999 9999
HIST B 10000 10000
HIST A 0 1
HIST A 1 10000
12 rows selected.
Elapsed: 00:00:00.00