如何理解ORACLE表统计信息与列统计信息、索引统计信息

如何理解ORACLE表统计信息与列统计信息、索引统计信息，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

ORACLE表统计信息与列统计信息

我们在分析某些语句的性能时，会分析一些信息。像表、列、索引、直方图等等，本篇主要讲表与列、索引的统计信息收集与分析。

一、表统计信息

1. 表已创建，需要收集统计信息

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ,

                                TABNAME => ,

                                ESTIMATE_PERCENT => 100,

METHOD_OPT => for all columns size skewonly,

                                CASCADE => TRUE);

END;

 2. 查看表的统计信息用dba_tab_statistics。

col OWNER for a16

col table_name for a20

col PARTITION_NAME for a18

col OBJECT_TYPE for a12

col LAST_ANALYZED for a20

SELECT OWNER,TABLE_NAME,PARTITION_NAME,OBJECT_TYPE,NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,to_char(LAST_ANALYZED,yyyy-mm-dd hh34:mi:ss) LAST_ANALYZED,USER_STATS

FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = MSTB_ORDER_HEADER ;

OWNER            TABLE_NAME  PARTITION_NAME OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN LAST_ANALYZED USER_S

---------------- ----------- -------------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------- ------

POS_ORDER        MSTB_ORDER_                TABLE          40445748    1928239            0          0          0         317 2016-01-07 17 NO

POS_ORDER        MSTB_ORDER_ P_MAXVALUE     PARTITION             1         19            0          0          0         197 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2009_10  PARTITION             0          0            0          0          0           0 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2009_11  PARTITION             0          0            0          0          0           0 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2009_12  PARTITION             0          0            0          0          0           0 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2012_1   PARTITION             0          0            0          0          0           0 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2012_10  PARTITION         73496       3299            0          0          0         260 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2012_11  PARTITION         69908       3138            0          0          0         259 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2012_12  PARTITION         66918       3018            0          0          0         260 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2012_6   PARTITION         45502       1940            0          0          0         256 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2012_7   PARTITION         53236       2279            0          0          0         256 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2012_8   PARTITION         52568       2279            0          0          0         254 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2012_9   PARTITION         71614       3219            0          0          0         260 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2013_1   PARTITION         72958       3293            0          0          0         260 2016-01-07 16 NO

POS_ORDER        MSTB_ORDER_ P_MOH_2013_10  PARTITION         75330       3449            0          0          0         261 2016-01-07 16 NO

关于每一列的解释联机文档上都有(http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2114.htm#REFRN20376)

这里blocks是高水位以下的数据块数，empty_blocks是高水位以上的数据块数。

Dbms_stats不计算EMPTY_BLOCKS、AVG_SPACE、CHAIN_CNT。可以使用ANALYZE收取(ANALYZE TABLE <tabname> COMPUTE STATISTICS)</tabname<>

chain_cnt字段表示行迁移和行链接的数量信息.

二、列统计信息

COLUMN_NAME         NUM_DISTINCT LOW_VALUE       HIGH_VALUE          DENSITY  NUM_NULLS AVG_COL_LEN HISTOGRAM       NUM_BUCKETS

------------------- ------------ --------------- ---------------- ---------- ---------- ----------- --------------- -----------

ORIGINAL_ORDER_PV           3707 80              C24C5F19          .00026976   40419026           2 NONE                      1

RSVST3                         4 4230363235      4230363238              .25   40194414           2 NONE                      1

INVOICE_FLAG                   2 80              C102             4.7902E-08   30012940           2 FREQUENCY                 2

INVOICE_REPRINT                2 80              C102             1.7177E-07   37531990           2 FREQUENCY                 2

POSWTO_FLAG                    1 80              80                        1          0           2 NONE                      1

INVOICE_SALES_DATE           705 78720106010101  78730A1A010101   .002169197   32955696           3 HEIGHT BALANCED         254

ORDER_SUM_QUANTITY          1400 C102            C4025A645B       .000714286    9823640           3 NONE                      1

RECIEVABLE_AMOUNT          81810 3C1E2E5B66      C50A1C143415     .000844595          0           4 HEIGHT BALANCED         254

ORDER_BV                  158632 3C640B66        C508590C3B35     6.3039E-06          0           4 NONE                      1

ORDER_PV                  167806 3D582C5166      C4400707450B     5.9593E-06          0           4 NONE                      1

SALE_DATE                   1293 78700206010101  78C70C04010101   .001996008          0           8 HEIGHT BALANCED         254

REGION_CODE                    2 303530          333630           1.2352E-08          0           4 FREQUENCY                 2

SHOP_CODE                    318 3031            6E756C6C          .00304878          0           5 HEIGHT BALANCED         254

SHIPPING_WAREHOUSE           389 3031            5443305A         .002808989          0           5 HEIGHT BALANCED         254

字段解释，可参考联机文档（http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_2101.htm#REFRN20275）

统计信息解释一下：

NUM_DISTINCT：该列中唯一值的数量。

LOW_VALUE：该列的最小值。显示为内部存储格式。对于字符串列，只存储前32字节。

HIGH_VALUE：该列的最大值。显示为内部存储格式。对于字符串列，只存储前32字节。

DENSITY：0到1之间的一个小数。接近0表示对于该列的过滤操作能去掉大多数的行。接近1表示对于该列的过滤操作起不到什么作用。如果没有直方图，DENSITY=1/NUM_DISTINCT。

HISTOGRAM：表明是否有直方图信息，如果有，是什么类型？FREQUENCY表示频率类型，HEIGHT BALANCED表示平均分布类型；如果没有，则为NONE。

NUM_BUCKETS：直方图里的桶数。它表示一组同类的数值放在一起。直方图最少由一个桶组成。如果没有直方图，则为1，最大桶数为254。

这里的LOW_VALUE和HIGH_VALUE都是内部格式，所以必须转换为可读懂的格式，有两种方法。

1）使用工具包utl_raw提供的函数cast_to_binary_double、cast_to_binary_float、cast_to_binary_integer、cast_to_number、cast_to_nvarchar2、cast_to_raw和cast_to_varchar2。这些函数就是把内部存储格式转换为实际值。

SELECT UTL_RAW.CAST_TO_NUMBER(LOW_VALUE), UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE)

  FROM DBA_TAB_COL_STATISTICS

WHERE TABLE_NAME = MSTB_ORDER_HEADER

  AND COLUMN_NAME = GROUP_ORDER_ID;

UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE)

--------------------------------- ----------------------------------

0                            7821636

2) 使用dbms_stats提供的过程convert_raw_value、convert_raw_value_nvarchar和convert_raw_value_rowid。该过程不能直接在SQL语句中使用，通常只用于PL/SQL程序中。

DECLARE

L_LOW_VALUE  DBA_TAB_COL_STATISTICS.LOW_VALUE%TYPE;

  L_HIGH_VALUE DBA_TAB_COL_STATISTICS.HIGH_VALUE%TYPE;

  L_VAL1       T.VAL1%TYPE;

BEGIN

SELECT LOW_VALUE, HIGH_VALUE

    INTO L_LOW_VALUE, L_HIGH_VALUE

    FROM DBA_TAB_COL_STATISTICS

WHERE TABLE_NAME = MSTB_ORDER_HEADER

  AND COLUMN_NAME = GROUP_ORDER_ID;

  DBMS_STATS.CONVERT_RAW_VALUE(L_LOW_VALUE, L_VAL1);

DBMS_OUTPUT.PUT_LINE(low_value: || L_VAL1);

  DBMS_STATS.CONVERT_RAW_VALUE(L_HIGH_VALUE, L_VAL1);

DBMS_OUTPUT.PUT_LINE(high_value: || L_VAL1);

END;

/

UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE)

--------------------------------- ----------------------------------

0                            7821636

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。