Oracle教程 2023-02-27

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

本站中文解释

Oracle视图ALL_TAB_HISTOGRAMS用来表示系统中各个标量统计信息的直方图。它可以用来存储用户数据信息的直方图，可以显示某一列的数据的值分布情况。它可以提升SQL使用分析函数、索引用途等的性能，并有助于分析表中的数据。

ALL_TAB_HISTOGRAMS中的列含义包括：OWNER（所属的用户）、TABLE_NAME（视图或表名）、COLUMN_NAME（列名）、ENDPOINT_NUMBER（端点号）、ENDPOINT_ACTUAL_VALUE（端点实际值）、NUM_DISTINCT（不重复值数）、LAST_ANALYZED（上次分析时间）、FRACTION_DISTINCT（不重复值占比）。

可以使用下面的SQL查询ALL_TAB_HISTOGRAMS来查看表或视图中直方图信息：

SELECT * FROM ALL_TAB_HISTOGRAMS WHERE TABLE_NAME=’表名’ AND COLUMN_NAME=’列名’;

其中TABLE_NAME和COLUMN_NAME是必填项。这可以给用户带来实用的信息，帮助用户更好的改善查询性能。

官方英文解释

ALL_TAB_HISTOGRAMS describes histograms on tables and views accessible to the current user.

The ALL_TAB_HISTOGRAMS view may contain a one-bucket histogram, which in fact signifies “No histogram” to the Oracle Database software. Therefore, it should not be queried to indicate the presence or absence of a histogram on a particular column. Instead, query the value of column HISTOGRAM in the ALL_TAB_COL_STATISTICS view.

Related Views

DBA_TAB_HISTOGRAMS describes histograms on all tables and views in the database.
USER_TAB_HISTOGRAMS describes histograms on all tables and views owned by the current user. This view does not display the OWNER column.

Note:

These views are populated only if you collect statistics on the table using the DBMS_STATS package. For more information, see Oracle Database PL/SQL
Packages and Types Reference.

Column	Datatype	Description
`OWNER`	`VARCHAR2(128)`	Owner of the table
`TABLE_NAME`	`VARCHAR2(128)`	Name of the table
`COLUMN_NAME`	`VARCHAR2(4000)`	Column name or attribute of the object type column
`ENDPOINT_NUMBER`	`NUMBER`	Histogram bucket number
`ENDPOINT_VALUE`	`NUMBER`	Normalized endpoint value for this bucket
`ENDPOINT_ACTUAL_VALUE`	`VARCHAR2(4000)`	Actual (not normalized) string value of the endpoint for this bucket
`ENDPOINT_ACTUAL_VALUE_RAW`	`RAW(1000)`	Endpoint actual value in raw format
`ENDPOINT_REPEAT_COUNT`	`NUMBER`	Frequency of the endpoint (applies only to hybrid histograms, and is set to 0 for other histogram types)
`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.

本站中文解释

官方英文解释

相关推荐