Histograms

Histograms 就是柱状图
在你不收集Histograms信息的时候
如果存在一个表 表中的记录从1到9，一共900条记录
由于没有收集Histograms信息
oracle会假设记录是平均分配的 就是说1有100条，2有100条……

而实际上1有800条，其他的100条
这样就会存在问题
在索引的选择上 对1的查询用全表扫描会更好 而对于其他的值用索引可能效果会更好

这时候就需要收集Histograms信息
告诉oracle数据是倾斜
不是平均分布的 Normal0falsefalsefalseMicrosoftInternetExplorer4[@more@]

Using Histograms

In general, create histograms on columns that are used frequently in WHERE clauses of queries and have a highly skewed data distribution.

在下列条件下，Histograms对列没有作用：

• 该列上用的是绑定变量.
• 该列数据均衡分布.
• .该列是unique且只用了相等谓词

10-bucket：

Figure 3-1 Histogram with Uniform Distribution

Figure 3-2 Histogram with Non-Uniform Distribution

The default number of buckets for a histogram is 75. This value provides an appropriate level of detail for most data distributions.

ENDPOINT_NUMBER   ENDPOINT_VALUE

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

          1365                 4

          1370                 5

          2124                 8

          2228                18

Table 3-5 ENDPOINT_NUMBER Differences

Bucket (values)	ENDPOINT_NUMBER Difference	Number of Values in Bucket
1 (0 to 4)	N/A	N/A
2 (4 to 5)	1370 - 1365	5
3 (5 to 8)	2124 - 1370	754
4 (8 to 18)	2228 - 2124	104

Table 3-5 shows that the buckets hold very different numbers of values. The data is skewed: 754 values are between 5 and 8, but only 104 are between 8 and 18. More buckets should be used.

Analyze 里的语句对histogram更好的理解

Normal0falsefalsefalseMicrosoftInternetExplorer4

ANALYZE

• COMPUTE: This option calculates exact statistics. It performs a full table scan and several calculations. For large tables, this operation can take a considerable amount of time.

• ESTIMATE: You estimate statistics with this option. If you use this option with a suitable sample of the data, it is almost as reliable as the COMPUTE option.When you want to clear the collected statistics, you can use the DELETE option. You do not need to use DELETE option before reanalyzing an object, because existing statistics are overwritten.

The FOR Clause

The FOR clause offers the following options:

• FOR TABLE: Restricts the statistics collected to only table statistics rather than table and column statistics.

• FOR COLUMNS: Restricts the statistics collected to only column statistics for the specified columns, rather than for all columns and attributes.

• FOR ALL COLUMNS: collects column statistics for all columns.

• FOR ALL INDEXED COLUMNS: Collects column statistics for all indexed columns in the table.

• FOR ALL [LOCAL] INDEXES: Specifies that all indexes associated with the table will be analyzed. LOCAL specifies that all local index partitions are analyzed.

The SIZE Clause

The SIZE clause specifies the maximum number of histogram buckets. The default value is 75, and the maximum value is 254.

Normal0falsefalsefalseMicrosoftInternetExplorer4

Normal0falsefalsefalseMicrosoftInternetExplorer4

Normal0falsefalsefalseMicrosoftInternetExplorer4

例子：

exec dbms_stats.gather_table_stats('e3','article',method_opt=>'FOR COLUMNS id');

对表aritcle进行分析，并分析id列。如果去掉后面的method_opt=>'FOR COLUMNS id'，就会分析表和全部列。

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

转载于:http://blog.itpub.net/14377/viewspace-1060105/