Oracle教程 2023-02-23

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

本站中文解释

oracle视图ALL_TAB_COL_STATISTICS用于查询当前用户下的所有表和字段的统计信息，包括字段值的个数、最大值、最小值、平均值、估计值等。此视图为用户以及DBA提供了一种简单而高效的数据健康分析工具。

要使用ALL_TAB_COL_STATISTICS视图，可以执行以下查询语句：
SELECT table_name, column_name,num_distinct,histogram,
last_analyzed
FROM all_tab_col_statistics
WHERE columns_owner = ‘用户名’;

上述查询语句用于查询指定用户下的所有表及字段的统计信息，包括表名、字段名、独立值总数、直方图以及最后一次分析信息。

官方英文解释

ALL_TAB_COL_STATISTICS displays column statistics and histogram information extracted from ALL_TAB_COLUMNS.

Related Views

DBA_TAB_COL_STATISTICS displays such information extracted from “DBA_TAB_COLUMNS”.
USER_TAB_COL_STATISTICS displays such information extracted from “USER_TAB_COLUMNS”. This view does not display the OWNER column.

Column	Datatype	Description
`OWNER`	`VARCHAR2(128)`	Owner of the table
`TABLE_NAME`	`VARCHAR2(128)`	Name of the table
`COLUMN_NAME`	`VARCHAR2(128)`	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
`LAST_ANALYZED`	`DATE`	Date on which this column was most recently analyzed
`SAMPLE_SIZE`	`NUMBER`	Sample size used in analyzing this column
`GLOBAL_STATS`	`VARCHAR2(3)`	`GLOBAL_STATS` will be `YES` if statistics are gathered or incrementally maintained, otherwise it will be `NO`
`USER_STATS`	`VARCHAR2(3)`	Indicates whether statistics were entered directly by the user (`YES`) or not (`NO`)
`NOTES`	`VARCHAR2(99)`	Describes some additional properties of the statistics. For example: A value of `INCREMENTAL` indicates that the global statistics are derived from synopses, that is, the global statistics are incrementally maintained. A value of `STATS_ON_CONVENTIONAL_LOAD` indicates that the statistics are obtained by online statistics gathering for conventional DML.
`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`
`SCOPE`	`VARCHAR2(7)`	The value is `SHARED` for statistics gathered on any table other than global temporary tables. For a global tempoary table, the possible values are: `SESSION` – Indicates that the statistics are session-specific `SHARED` – Indicates that the statistics are shared across all sessions See Oracle Database PL/SQL Packages and Types Reference for information about using the `GLOBAL_TEMP_TABLE_STATS` preference of the `DBMS_STATS` package to control whether to gather session or shared statistics for global temporary tables.

本站中文解释

官方英文解释

相关推荐