Oracle 视图 ALL_SUBPART_COL_STATISTICS 官方解释，作用，如何使用详细说明

本站中文解释

ALL_SUBPART_COL_STATISTICS是Oracle数据库中的一个视图，可以提供有关分区表中单个列统计信息的详细信息。它显示列上各个近似分布值（等级）或精确值（细裂）的统计信息，以及该列上的一般统计信息（例如最小值、最大值、众数和均值）。

ALL_SUBPART_COL_STATISTICS的用途是一些查询优化技术，当可能有分区条件的时候，可以允许Oracle ORACLE知道数据字典中关于单个列分区的信息，这样它就可以找到最佳的查询计划。

使用ALL_SUBPART_COL_STATISTICS视图，用户可以了解每个分区表上的分区列的统计信息（最小值、最大值、众数和平均数），以及每个分区列上近似级别值（近似值）和细裂值（比例）的统计信息。例如：

SELECT *
FROM ALL_SUBPART_COL_STATISTICS
WHERE table_name = ‘MY_TABLE’
AND column_name = ‘MY_PARTITION_COLUMN’;

官方英文解释

ALL_SUBPART_COL_STATISTICS describes column statistics and histogram information for subpartitions of partitioned objects accessible to the current user.

Related Views

DBA_SUBPART_COL_STATISTICS provides this information for all subpartitions in the database.
USER_SUBPART_COL_STATISTICS provides this information for subpartitions of all partitioned objects owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(128)`	`NOT NULL`	Owner of the table
`TABLE_NAME`	`VARCHAR2(128)`	`NOT NULL`	Name of the table
`SUBPARTITION_NAME`	`VARCHAR2(128)`		Table subpartition name
`COLUMN_NAME`	`VARCHAR2(4000)`		Column name
`NUM_DISTINCT`	`NUMBER`		Number of distinct values in the column
`LOW_VALUE`	`RAW(1000)`		Low value in the column
`HIGH_VALUE`	`RAW(1000)`		High value in the column
`DENSITY`	`NUMBER`		If a histogram is available on `COLUMN_NAME`, then this column displays the selectivity of a value that spans fewer than 2 endpoints in the histogram. It does not represent the selectivity of values that span 2 or more endpoints. If a histogram is not available on `COLUMN_NAME`, then the value of this column is `1`/`NUM_DISTINCT`.
`NUM_NULLS`	`NUMBER`		Number of NULLs in the column
`NUM_BUCKETS`	`NUMBER`		Number of buckets in histogram for the column
`SAMPLE_SIZE`	`NUMBER`		Sample size used in analyzing this column
`LAST_ANALYZED`	`DATE`		Date on which this column was most recently analyzed
`GLOBAL_STATS`	`VARCHAR2(3)`		`GLOBAL_STATS` will be `YES` if statistics have been gathered or `NO` if statistics have not been gathered
`USER_STATS`	`VARCHAR2(3)`		Indicates whether statistics were entered directly by the user (`YES`) or not (`NO`)
`NOTES`	`VARCHAR2(41)`		Describes some additional properties of the statistics. For example, if the value is `INCREMENTAL`, the global statistics are derived from synopses, that is, the global statistics are incrementally maintained.
`AVG_COL_LEN`	`NUMBER`		Average length of the column (in bytes)
`HISTOGRAM`	`VARCHAR2(15)`		Indicates existence/type of histogram: `NONE` `FREQUENCY` `HEIGHT BALANCED` `HYBRID` `TOP-FREQUENCY`