Oracle教程 2023-02-23

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

本站中文解释

ALL_IND_STATISTICS视图提供了在聚簇索引中各个列的统计信息。用户可以通过ALL_IND_STATISTICS视图查询表中列的统计信息，如表的块数、行数、全局高速缓存缩放量等，以便进行优势计划。查询ALL_IND_STATISTICS视图的步骤如下：

(1) 首先可以运行以下查询语句来检索有关表的所有聚簇索引的统计信息

SELECT * FROM ALL_IND_STATISTICS WHERE TABLE_NAME=’表名’;

(2) 如果要查询特定的列的统计信息，可以增加一句过滤数据，如：

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

(3) 如果要检索有关表中特定索引的统计信息，可以运行以下SQL语句：

SELECT * FROM ALL_IND_STATISTICS WHERE TABLE_NAME=’表名’ AND INDEX_NAME=’索引名’;

使用ALL_IND_STATSITICS视图可以知道聚簇索引中各个列的统计信息，当需要优化和调整表的索引时，就可以根据统计信息来决定优化的选择，以便更有效地改善查询的性能。

官方英文解释

ALL_IND_STATISTICS displays optimizer statistics for the indexes on the tables accessible to the current user collected using the DBMS_STATS package.

Related Views

DBA_IND_STATISTICS displays optimizer statistics for all indexes in the database.
USER_IND_STATISTICS displays optimizer statistics for the indexes on the tables owned by the current user. This view does not display the OWNER column.

Column	Datatype	Description
`OWNER`	`VARCHAR2(128)`	Owner of the index
`INDEX_NAME`	`VARCHAR2(128)`	Name of the index
`TABLE_OWNER`	`VARCHAR2(128)`	Owner of the indexed object
`TABLE_NAME`	`VARCHAR2(128)`	Name of the indexed object
`PARTITION_NAME`	`VARCHAR2(128)`	Name of the partition
`PARTITION_POSITION`	`NUMBER`	Position of the partition within the index
`SUBPARTITION_NAME`	`VARCHAR2(128)`	Name of the subpartition
`SUBPARTITION_POSITION`	`NUMBER`	Position of the subpartition within the partition
`OBJECT_TYPE`	`VARCHAR2(12)`	Type of the object: `INDEX` `PARTITION` `SUBPARTITION`
`BLEVEL`	`NUMBER`	B-Tree level
`LEAF_BLOCKS`	`NUMBER`	Number of leaf blocks in the index
`DISTINCT_KEYS`	`NUMBER`	Number of distinct keys in the index
`AVG_LEAF_BLOCKS_PER_KEY`	`NUMBER`	Average number of leaf blocks per key
`AVG_DATA_BLOCKS_PER_KEY`	`NUMBER`	Average number of data blocks per key
`CLUSTERING_FACTOR`	`NUMBER`	Indicates the amount of order of the rows in the table based on the values of the index. If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks. If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
`NUM_ROWS`	`NUMBER`	Number of rows in the index
`AVG_CACHED_BLOCKS`	`NUMBER`	Average number of blocks in the buffer cache
`AVG_CACHE_HIT_RATIO`	`NUMBER`	Average cache hit ratio for the object
`SAMPLE_SIZE`	`NUMBER`	Sample size used in analyzing the index
`LAST_ANALYZED`	`DATE`	Date of the most recent time the index was analyzed
`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`)
`STATTYPE_LOCKED`	`VARCHAR2(5)`	Type of statistics lock
`STALE_STATS`	`VARCHAR2(3)`	Whether statistics for the object are stale or not
`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.

本站中文解释

官方英文解释

相关推荐