本文共 6072 字，大约阅读时间需要 20 分钟。
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 processedSQL> 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.28SQL> 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 processedSQL> 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 1000012 rows selected.
Elapsed: 00:00:00.00
转载地址：http://vnilx.baihongyu.com/