Oracle 直方图

文章整理来源于：
剑破冰山之Oracle开发
http://www.dbform.com/html/2010/1200.html

--下面来先关组下ORACLE的预测行对执行计划的影响。
DROP TABLE T PURGE;
CREATE TABLE T(ID,COL1,COL2)
AS
SELECT ROWNUM,CASE WHEN ROWNUM >100 THEN 200 ELSE ROWNUM END,ROWNUM
FROM DUAL
CONNECT BY LEVEL <=10000;

CREATE INDEX T_COL1 ON T(COL1);
admin@ORCL> SELECT COUNT(*) FROM T WHERE COL1=200;

  COUNT(*)
----------
      9900

admin@ORCL> SELECT COUNT(*) FROM T WHERE COL1<>200;

  COUNT(*)
----------
       100

--收集表统计信息，但不收集直方图(SIZE 1即不收集)
admin@ORCL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(
  3  'ADMIN',
  4  'T',
  5  CASCADE=>TRUE,
  6  ESTIMATE_PERCENT=>100,
  7  METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
  8  NO_INVALIDATE=>FALSE);
  9  END;
 10  /

--查看执行计划,返回的行数实际上应为为9900，而Oracle预估的返回行数为99，导致Oracle认为走索引效率比较高。这是个比较错误的执行计划。


admin@ORCL> SELECT * FROM T WHERE COL1 = 200;
已选择9900行。
执行计划
----------------------------------------------------------
Plan hash value: 1192298089
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    99 |   990 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |    99 |   990 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_COL1 |    99 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COL1"=200)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1363  consistent gets
          0  physical reads
          0  redo size
     212731  bytes sent via SQL*Net to client
       7634  bytes received via SQL*Net from client
        661  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9900  rows processed

--了解下Oracle在没有收集直方图的情况下，如何计算出COL1 = 200的返回行数
admin@ORCL> SELECT COUNT(DISTINCT COL1) FROM T;

COUNT(DISTINCTCOL1)
-------------------
                101
admin@ORCL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
     10000
--Oracle 会认为COL1每个值都是均匀分布的
admin@ORCL> select round(10000/101) from dual;

ROUND(10000/101)
----------------
              99

--全面收集行的直方图

admin@ORCL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(
  3  'ADMIN',
  4  'T',
  5  CASCADE=>TRUE,
  6  ESTIMATE_PERCENT=>100,
  7  METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',
  8  NO_INVALIDATE=>FALSE);
  9  END;
 10  /
 
--Oracle选择了正确的执行计划：全表扫描，逻辑读也从1363->685
admin@ORCL> SELECT * FROM T WHERE COL1 = 200;
已选择9900行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9900 | 99000 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  9900 | 99000 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"=200)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        685  consistent gets
          0  physical reads
          0  redo size
     212731  bytes sent via SQL*Net to client
       7634  bytes received via SQL*Net from client
        661  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9900  rows processed


--我们来仔细研究下Oracle是如何存储列的直方图信息
--直方图的类型有三种

None: 没有直方图
Frequency: 频率直方图
HEIGHT BALANCED: 等高直方图

--当该列的distinct值数量<=bucket数量时，直方图的类型为FREQUENCY,否者为HEIGHT BALANCE
admin@ORCL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T' AND OWNER = 'ADMIN';

COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- ------------ ----------- ---------------
ID                          10000         254 HEIGHT BALANCED
COL1                          101         101 FREQUENCY
COL2                        10000         254 HEIGHT BALANCED

--col1的直方图类型为Frequency,
--在DBA_TAB_HISTOGRAMS视图中，字段endpoint_value就对应字段COL1的值，
--而endpoint_number对应字段COL1值的记录条数。注意：这里的记录条数是累加的。

admin@ORCL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_TAB_HISTOGRAMS
  3   WHERE OWNER = 'ADMIN'
  4     AND TABLE_NAME = 'T'
  5     AND COLUMN_NAME = 'COL1';


COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- --------------- --------------
COL1                               1              1
COL1                               2              2
COL1                               3              3
COL1                               4              4
COL1                               5              5
COL1                               6              6
COL1                               7              7
COL1                               8              8
COL1                               9              9
COL1                              10             10
....                              ..             ..
COL1                              98             98
COL1                              99             99
COL1                             100            100
COL1                           10000            200

--Frequency直方图的统计方式应该就等同于下面的sql
admin@ORCL> SELECT COL1 AS ENDPONT_VALUE,COUNT(*) AS CNT,SUM(COUNT(*)) OVER(ORDER BY COL1) AS ENDPOINT_NUM FROM T GROUP BY COL1 ORDER BY COL1

ENDPONT_VALUE        CNT ENDPOINT_NUM
------------- ---------- ------------
            1          1            1
            2          1            2
            3          1            3
            4          1            4
            5          1            5
            6          1            6
            7          1            7
            8          1            8
            9          1            9
           10          1           10
           ..          .           ..
           98          1           98
           99          1           99
          100          1          100
          200       9900        10000

已选择101行。
--我们看看直方图类型为HEIGHT BALANCE类型时，是如何存储的
--为了方便演示，这里SIZE 250表示250个bucket来存放数据
admin@ORCL> BEGIN
  2   DBMS_STATS.GATHER_TABLE_STATS(
  3   'ADMIN',
  4   'T',
  5   CASCADE=>TRUE,
  6   ESTIMATE_PERCENT=>100,
  7   METHOD_OPT=>'FOR ALL COLUMNS SIZE 250',
  8   NO_INVALIDATE=>FALSE);
  9  END;
 10  /

PL/SQL 过程已成功完成。

--查看直方图信息
admin@ORCL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T' AND OWNER = 'ADMIN';

COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- ------------ ----------- ---------------
ID                          10000         250 HEIGHT BALANCED
COL1                          101         101 FREQUENCY
COL2                        10000         250 HEIGHT BALANCED


admin@ORCL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, END
  2    FROM DBA_TAB_HISTOGRAMS
  3   WHERE OWNER = 'ADMIN'
  4     AND TABLE_NAME = 'T'
  5     AND COLUMN_NAME = 'ID';

COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- --------------- --------------
ID                                 0              1
ID                                 1             40
ID                                 2             80
ID                                 3            120
ID                                 4            160
ID                                 5            200
ID                                 6            240
ID                                 7            280
ID                                 8            320
ID                                 9            360
ID                                10            400
..                                ..            ...
ID                               248           9920
ID                               249           9960
ID                               250          10000

--这里的ENDPOINT_NUMBER不再表示记录的条数，它标示BUCKET(桶)的编号，ENDPOINT_VALUE的算法如下：
--记录条数为10000条，共分配到250个桶中，那么每个桶的数据量为40条记录
第一个ENDPOINT_VALUE的值相当于如下SQL的返回值。
admin@ORCL> select max(ID) from (select ID from t order by ID) where rownum<=40;

   MAX(ID)
----------
        40

第二个ENDPOINT_VALUE的值相当于如下SQL的返回值。
admin@ORCL> select max(ID) from (select ID from t order by ID) where rownum<=40*2;

   MAX(ID)
----------
        80

第三个ENDPOINT_VALUE的值相当于如下SQL的返回值。
admin@ORCL> select max(ID) from (select ID from t order by ID) where rownum<=40*3;

   MAX(ID)
----------
       120
/*
依次类推。正是这样的信息告诉了Oracle数据的分布情况，试想一下，如果连续3个bucket的ENDPOINT_VALUE值都10，
那么也就是说至少有2个bucket中的记录N2字段都是10，也就是说ID=10的记录至少有80条，越多的bucket有越多相同的ENDPOINT_VALUE值，
就表明数据分布越不均匀。
有一点需要额外注意的是：如果某几个bucket的ENDPOINT_VALUE值相同，那么在视图中只会记录最后一个bucket的信息。
*/

--何时应该收集直方图

首先我们要明确直方图信息是有存在的必要的，但是只应该存在在那些应该要存在的列上。或者说我们希望对不不同的字段值走不同的执行计划时
才考虑收集直方图。

--反过来说，什么情况下我们不需要直方图呢？或者说直方图的存在是没有意义的呢？
1. 当此列不用于查询时，也就是这个字段永远不会出现在where条件中（注意：即使是用于表连接那也算是出现在where条件中）。
2. 当此列无论给予什么比较值，我们都希望永远是一种执行计划时。