ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 直方图测试

直方图测试

原创 Linux操作系统 作者:lsq_008 时间:2009-02-19 07:05:25 0 删除 编辑

首先看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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1235534