Oracle 视图 ALL_IND_STATISTICS 官方解释,作用,如何使用详细说明
  iDU31ygkXmx7 2023年11月21日 16 0

本站中文解释

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 NULL 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.

See Also:

  • “DBA_IND_STATISTICS”

  • “USER_IND_STATISTICS”

  • Oracle Database PL/SQL
    Packages and Types Reference
    for more information about the DBMS_STATS package

【版权声明】本文内容来自摩杜云社区用户原创、第三方投稿、转载,内容版权归原作者所有。本网站的目的在于传递更多信息,不拥有版权,亦不承担相应法律责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@moduyun.com

  1. 分享:
最后一次编辑于 2023年11月21日 0

暂无评论

iDU31ygkXmx7