直方图测试

首先看test表上deptno列的数据分布情况：

SQL> select count(*) from test;
 
  COUNT(*)
----------
   1000000

SQL> select distinct deptno from test;
 
    DEPTNO
----------
         1
         2
         4
         3

SQL> select count(*),count(*)/1000000 from test where deptno=1;
 
  COUNT(*) COUNT(*)/1000000
---------- ----------------
      1000             .001
 
SQL> select count(*),count(*)/1000000 from test where deptno=2;
 
  COUNT(*) COUNT(*)/1000000
---------- ----------------
      9000             .009
 
SQL> select count(*),count(*)/1000000 from test where deptno=3;
 
  COUNT(*) COUNT(*)/1000000
---------- ----------------
     90000              .09
 
SQL> select count(*),count(*)/1000000 from test where deptno=4;
 
  COUNT(*) COUNT(*)/1000000
---------- ----------------
    900000               .9

显然，在deptno列上有4个值，每个值的行数占总行数不同的比率，对表test做包含了直方图信息的统计信息收集：

SQL> BEGIN
  DBMS_STATS.GATHER_table_STATS (OWNNAME => 'SCOTT', TABNAME => 'TEST',
  METHOD_OPT => 'FOR COLUMNS SIZE auto deptno');
END;
/  2    3    4    5 
 
PL/SQL procedure successfully completed.

SQL> SELECT column_name, num_distinct, num_buckets, histogram
  FROM USER_TAB_COL_STATISTICS
 WHERE table_name = 'TEST' AND column_name = 'DEPTNO';
  2    3 
COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
DEPTNO                                    4           4 FREQUENCY

SQL> SELECT endpoint_number, endpoint_value
  FROM USER_HISTOGRAMS
 WHERE table_name = 'TEST' and column_name = 'DEPTNO'
  ORDER BY endpoint_number  2    3    4  ;
 
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              4              1
             51              2
            547              3
           5536              4

分别以不同的deptno值作为where条件进行查询，查看相应的执行计划：

SQL> select * from test where deptno=1;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1182089618
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   706 | 26828 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |   706 | 26828 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST2 |   723 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=1)
 
SQL> select * from test where deptno=2;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1182089618
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  8290 |   307K|    69   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |  8290 |   307K|    69   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST2 |  8490 |       |    19   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=2)
 
SQL> select * from test where deptno=3;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1182089618
 
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 87486 |  3246K|   710   (1)| 00:00:09 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      | 87486 |  3246K|   710   (1)| 00:00:09 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST2 | 89595 |       |   179   (1)| 00:00:03 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=3)
 
SQL> select * from test where deptno=4;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   879K|    31M|  1622   (2)| 00:00:20 |
|*  1 |  TABLE ACCESS FULL| TEST |   879K|    31M|  1622   (2)| 00:00:20 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTNO"=4)

SQL> set autot off

可见，在收集了直方图信息后，优化器能很好的根据列的值选择合适的执行计划。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-555613/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-555613/