2.170 ALL_IND_SUBPARTITIONS

ALL_IND_SUBPARTITIONS displays, for each index subpartition accessible to the current user, the subpartition-level partitioning information, the storage parameters for the subpartition, and various subpartition statistics generated by the DBMS_STATS package.

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(128)

NOT NULL

Owner of the index

INDEX_NAME

VARCHAR2(128)

NOT NULL

Name of the index

PARTITION_NAME

VARCHAR2(128)

Name of the partition

SUBPARTITION_NAME

VARCHAR2(128)

Name of the subpartition

HIGH_VALUE

LONG

Subpartition bound value expression

HIGH_VALUE_LENGTH

NUMBER

NOT NULL

Length of the subpartition bound value expression

PARTITION_POSITIONFoot 1

NUMBER

Position of the partition within the index

SUBPARTITION_POSITION

NUMBER

Position of a subpartition within a partition

STATUS

VARCHAR2(8)

Indicates whether the index partition is usable (USABLE) or not (UNUSABLE)

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

Minimum number of extents allowed in the segment

MAX_EXTENT

NUMBER

Maximum number of extents allowed in the segment

MAX_SIZE

NUMBER

Maximum number of blocks 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)

Indicates whether or not changes to the index are logged:

  • YES

  • NO

COMPRESSION

VARCHAR2(13)

Indicates whether this subpartition is compressed (ENABLED) or not (DISABLED)

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)

Buffer pool for the subpartition:

  • DEFAULT

  • KEEP

  • RECYCLE

  • NULL

FLASH_CACHE

VARCHAR2(7)

Database Smart Flash Cache hint to be used for subpartition blocks:

  • DEFAULT

  • KEEP

  • NONE

Solaris and Oracle Linux functionality only.

CELL_FLASH_CACHE

VARCHAR2(7)

Cell flash cache hint to be used for subpartition blocks:

  • DEFAULT

  • KEEP

  • NONE

See Also: Oracle Exadata Storage Server Software documentation for more information

USER_STATS

VARCHAR2(3)

Indicates whether statistics were entered directly by the user (YES) or not (NO)

GLOBAL_STATS

VARCHAR2(3)

GLOBAL_STATS will be YES if statistics have been gathered or NO if statistics have not been gathered

INTERVAL

VARCHAR2(3)

Indicates whether the partition is in the interval section of an interval partitioned table (YES) or whether the partition is in the range section (NO)

SEGMENT_CREATED

VARCHAR2(3)

Indicates whether the index subpartition segment has been created (YES) or not (NO); N/A indicates that this index is not subpartitioned

PARAMETERSFootref 1

VARCHAR2(1000)

For a domain index, the parameter string

Footnote 1

This column is available starting with Oracle Database 12c Release 2 (12.2.0.1).

See Also: