ALL_INDEXES

ALL_INDEXES describes indexes on all tables accessible to the user. To gather statistics for this view and the related views DBA_INDEXES and USER_INDEXES, use the SQL ANALYZE statement.

Note: Column names followed by an asterisk are populated only if you collect statistics on the index using the ANALYZE statement or the DBMS_STATS package.

Related Views

• DBA_INDEXES describes all indexes in the database.
• USER_INDEXES describes the indexes owned by the current user. This view does not display the OWNER column.

  Column	Datatype	NULL	Description
  OWNER	VARCHAR2(30)	NOT NULL	Owner of the index
  INDEX_NAME	VARCHAR2(30)	NOT NULL	Name of the index
  INDEX_TYPE	VARCHAR2(12)		Type of index (NORMAL, BITMAP, FUNCTION-BASED NORMAL, FUNCTION-BASED BITMAP, or DOMAIN)
  TABLE_OWNER	VARCHAR2(30)	NOT NULL	Owner of the indexed object
  TABLE_NAME	VARCHAR2(30)	NOT NULL	Name of the indexed object
  TABLE_TYPE	CHAR(5)		Type of the indexed object (for example, TABLE, CLUSTER)
  UNIQUENESS	VARCHAR2(9)		Whether the index is UNIQUE or NONUNIQUE
  COMPRESSION	VARCHAR2(11)		Whether the index is ENABLED or DISABLED
  PREFIX_LENGTH	NUMBER		Number of columns in the prefix of the compression key
  TABLESPACE_NAME	VARCHAR2(30)		Name of the tablespace containing the index
  INI_TRANS	NUMBER		Initial number of transactions
  MAX_TRANS	NUMBER		Maximum number of transactions
  INITIAL_EXTENT	NUMBER		Size of the initial extent
  NEXT_EXTENT	NUMBER		Size of secondary extents
  MIN_EXTENTS	NUMBER		Minimum number of extents allowed in the segment
  MAX_EXTENTS	NUMBER		Maximum number of extents allowed in the segment
  PCT_INCREASE	NUMBER		Percentage increase in extent size
  PCT_THRESHOLD	NUMBER		Threshold percentage of block space allowed per index entry
  INCLUDE_COLUMN	NUMBER		Column ID of the last column to be included in index-organized table primary key (non-overflow) index. This column maps to the COLUMN_ID column of the *_TAB_COLUMNS data dictionary views.
  FREELISTS	NUMBER		Number of process freelists allocated to this segment
  FREELIST_GROUPS	NUMBER		Number of freelist groups allocated to this segment
  PCT_FREE	NUMBER		Minimum percentage of free space in a block
  LOGGING	VARCHAR2(3)		Logging information
  BLEVEL*	NUMBER		B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.
  LEAF_BLOCKS*	NUMBER		Number of leaf blocks in the index
  DISTINCT_KEYS*	NUMBER		Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS)
  AVG_LEAF_BLOCKS_PER_KEY*	NUMBER		Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1.
  AVG_DATA_BLOCKS_PER_KEY*	NUMBER		Average number of data blocks in the table that are pointed to by a distinct value in the index rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns.
  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.
  STATUS	VARCHAR2(8)		Whether a nonpartitioned index is VALID or UNUSABLE
  NUM_ROWS	NUMBER		Number of rows in the index
  SAMPLE_SIZE	NUMBER		Size of the sample used to analyze the index
  LAST_ANALYZED	DATE		Date on which this index was most recently analyzed
  DEGREE	VARCHAR2(40)		Number of threads per instance for scanning the index
  INSTANCES	VARCHAR2(40)		Number of instances across which the indexes to be scanned
  PARTITIONED	VARCHAR2(3)		Whether this index is partitioned (YES | NO)
  TEMPORARY	VARCHAR2(1)		Whether the index is on a temporary table
  GENERATED	VARCHAR2(1)		Whether the name of the index is system generated (Y|N)
  SECONDARY	VARCHAR2(1)		Whether the index is a secondary object created by the ODCIIndexCreate method of the Oracle9i Data Cartridge (Y|N)
  BUFFER_POOL	VARCHAR2(7)		Name of the default buffer pool to be used for the index blocks
  USER_STATS	VARCHAR2(3)		Whether the statistics were entered directly by the user
  DURATION	VARCHAR2(15)		Indicates the duration of a temporary table: SYS$SESSION: the rows are preserved for the duration of the session SYS$TRANSACTION: the rows are deleted after COMMIT Null for a permanent table
  PCT_DIRECT_ACCESS	NUMBER		For a secondary index on an index-organized table, the percentage of rows with VALID guess
  ITYP_OWNER	NUMBER		For a domain index, the owner of the indextype
  ITYP_NAME	VARCHAR2(30)		For a domain index, the name of the indextype
  PARAMETERS	VARCHAR2(1000)		For a domain index, the parameter string
  GLOBAL_STATS	VARCHAR2(3)		For partitioned indexes, indicates whether statistics were collected by analyzing index as a whole (YES) or were estimated from statistics on underlying index partitions and subpartitions (NO).
  DOMIDX_STATUS	VARCHAR		Reflects the status of the domain index NULL: the specified index is not a domain index VALID: the index is a valid domain index IDXTYP_INVLD: the indextype of this domain index is invalid
  DOMIDX_OPSTATUS	VARCHAR		Reflects the status of an operation that was performed on a domain index NULL: the specified index is not a domain index VALID: the operation performed without errors FAILED: the operation failed with an error
  FUNCIDX_STATUS	VARCHAR		Indicates the status of a function-based index NULL: this is not a function-based index ENABLED: the function-based index is enabled DISABLED: the function-based index is disabled