ALL_IND_SUBPARTITIONS

ALL_IND_SUBPARTITIONS describes, for each index subpartition accessible to the current user, the partition-level partitioning information, the storage parameters for the subpartition, and various partition statistics collected by ANALYZE statements.

Related Views

• DBA_IND_SUBPARTITIONS describes all index subpartitions in the database.
• USER_IND_SUBPARTITIONS describes the index subpartitions owned by the current user. This view does not display the INDEX_OWNER column.

  Column	Datatype	NULL	Description
  INDEX_OWNER	VARCHAR2(30)	NOT NULL	Owner of the index
  INDEX_NAME	VARCHAR2(30)	NOT NULL	Name of the index
  PARTITION_NAME	VARCHAR2(30)		Name of the partition
  SUBPARTITION_NAME	VARCHAR2(30)		Name of the subpartition
  SUBPARTITION_POSITION	NUMBER	NOT NULL	Position of a subpartition within a partition
  STATUS	VARCHAR2(8)		Whether index partition is usable or not
  TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace containing the partition
  PCT_FREE	NUMBER	NOT NULL	Minimum percentage of free space in a block
  INI_TRANS	NUMBER	NOT NULL	Initial number of transactions
  MAX_TRANS	NUMBER	NOT NULL	Maximum number of transactions
  INITIAL_EXTENT	NUMBER		Size of the initial extent in bytes
  NEXT_EXTENT	NUMBER		Size of secondary extents in bytes
  MIN_EXTENT	NUMBER	NOT NULL	Minimum number of extents allowed in the segment
  MAX_EXTENT	NUMBER	NOT NULL	Maximum number of extents allowed in the segment
  PCT_INCREASE	NUMBER	NOT NULL	Percentage increase in extent size
  FREELISTS	NUMBER		Number of process freelists allocated in this segment
  FREELIST_GROUPS	NUMBER		Number of process freelist groups allocated in this segment
  LOGGING	VARCHAR2(3)		Logging attribute of partition
  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 keys in the index partition
  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.
  NUM_ROWS	NUMBER		Number of rows in this index subpartition
  SAMPLE_SIZE	NUMBER		Sample size used in analyzing this subpartition
  LAST_ANALYZED	DATE		Date on which this partition was most recently analyzed
  BUFFER_POOL	VARCHAR2(7)		Default buffer pool for the subpartition
  USER_STATS	VARCHAR2(3)		Whether the statistics were entered directly by the user
  GLOBAL_STATS	VARCHAR2(3)		Indicates whether column statistics for the subpartition statistics were collected by analyzing the table as a whole (YES) or estimated from statistics gathered on partitions and subpartitions (NO).