直方图是描述表的列值数据分布的一种状态,它以桶(buckets)的形式存储。直方图根据列中值的不同和数据分布,又分为频率直方图(Frequency histograms and top frequency histograms)、高度平衡直方图(Height-Balanced histograms (legacy))和混合直方图(Hybrid histograms )。oracle优化器在默认的情况下会认为列中不同的值是均匀分布的,对于列值是非均匀分布的情况下,直方图可以帮助优化器精准的计算出filter或是各种join产生的rows的基数。
直方图是如何产生的呢?可以通过DBMS_STATS的方式收集表的统计信息。当一个SQL查询表的一个列时,oracle会根据这个SQL的workload(也就是列的数据量)自动的产生直方图信息。
直方图信息产生的过程是这样:1、通过DBMS_STATS把参数 METHOD_OPT设置为SIZE AUTO ;2、运行sql查询该表数据; 3、ORACLE DATABASE 会根据运行的SQLl判断和更新数据字典表SYS.COL_USAGE$ ;4、再次运行DBMS_STATS,让DBMS_STATS去查询字典表SYS.COL_USAGE$来判断那一列需要创建直方图信息。
实例1:database自动收集列的直方图信息
ORACLE DATABASE是选择直方图的类型,主要是通过多种不同的标准来的。 首先是 NDV(
Number of distinct values
),NDV是列中不相同的值的数量;另外就是 N, N是直方图桶(
histogram buckets
)的数量,最大是254个, 最后就是 P ,基线百分比,是根据直方图生产的桶数量来计算的,公式
(1–(1/
n
)) * 100。例如n=254,P=99.6。和直方图生产类型相关的另外一个参数是estimate_percent,这个参数默认值为AUTO_SAMPLE_SIZE,可以通过DBMS_STATS设置。
下图表示直方图选择的类型因素。
直方图是如何产生的呢?可以通过DBMS_STATS的方式收集表的统计信息。当一个SQL查询表的一个列时,oracle会根据这个SQL的workload(也就是列的数据量)自动的产生直方图信息。
直方图信息产生的过程是这样:1、通过DBMS_STATS把参数 METHOD_OPT设置为SIZE AUTO ;2、运行sql查询该表数据; 3、ORACLE DATABASE 会根据运行的SQLl判断和更新数据字典表SYS.COL_USAGE$ ;4、再次运行DBMS_STATS,让DBMS_STATS去查询字典表SYS.COL_USAGE$来判断那一列需要创建直方图信息。
实例1:database自动收集列的直方图信息
点击(此处)折叠或打开
- SQL> CREATE TABLE RECORD1 AS SELECT * FROM RECORD;
-
- Table created
- SQL> CREATE INDEX I_RECORD1_ENT ON RECORD1(ENTERPRISE_ID);
-
- Index created
-
- SQL> CREATE INDEX I_RECORD1_ENT_TM ON RECORD1(ENTERPRISE_ID,TEMPLATE_ID);
-
- Index created
-
- SQL> select table_name,column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where owner='MTS' and table_name='RECORD1';
-
- TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
- ------------------------------ ------------------------------ ------------ ----------- ---------------
- SQL> select count(*) from record1 where enterprise_id = 'SXdxyw'
- 2 ;
-
- COUNT(*)
- ----------
- 2710
-
- SQL> exec dbms_stats.gather_table_stats(ownname => 'mts',tabname => 'record1',method_opt => 'for all columns size auto');
-
- PL/SQL procedure successfully completed
-
- SQL> select table_name,column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where owner='MTS' and table_name='RECORD1';
TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------------------------ ------------ ----------- ---------------
RECORD1 ID 419313 1 NONE
RECORD1 ENTERPRISE_ID 741 254 HEIGHT BALANCED
RECORD1 OPERATOR_ID 7629 1 NONE
RECORD1 TEMPLATE_ID 1148 1 NONE
RECORD1 WORK_CODE 608 1 NONE
RECORD1 NAME 121552 1 NONE
RECORD1 CREATE_TIME 411904 1 NONE
RECORD1 ACTUAL_LONGITUDE 146240 1 NONE
RECORD1 ACTUAL_LATITUDE 145056 1 NONE
RECORD1 ACTUAL_LOCDESC 41248 1 NONE
RECORD1 CONTENT 50040 1 NONE
RECORD1 DELETE_STATE 2 1 NONE
RECORD1 CITY_NAME 3 1 NONE
RECORD1 COUNTY_NAME 3 1 NONE
RECORD1 PROVINCE_NAME 3 1 NONE
RECORD1 PIC_NUM 8 1 NONE
16 rows selected - SQL>
下图表示直方图选择的类型因素。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27039319/viewspace-2092773/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27039319/viewspace-2092773/