ALL_IND_PARTITIONS

2.171 ALL_IND_PARTITIONS

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

Related Views

DBA_IND_PARTITIONS describes all index partitions in the database.
USER_IND_PARTITIONS describes the index partitions owned by the current user. This view does not display the INDEX_OWNER column.

Column	Datatype	Description
`INDEX_OWNER`	`VARCHAR2(128)`	Owner of the index
`INDEX_NAME`	`VARCHAR2(128)`	Name of the index
`COMPOSITE`	`VARCHAR2(3)`	Indicates whether the partition belongs to a local index on a composite-partitioned table (`YES`) or not (`NO`)
`PARTITION_NAME`	`VARCHAR2(128)`	Name of the partition
`SUBPARTITION_COUNT`	`NUMBER`	If a local index on a composite-partitioned table, the number of subpartitions in the partition
`HIGH_VALUE`	`LONG`	Partition bound value expression
`HIGH_VALUE_LENGTH`	`NUMBER`	Length of the partition bound value expression
`PARTITION_POSITION`	`NUMBER`	Position of the partition within the index
`STATUS`	`VARCHAR2(8)`	Indicates whether the index partition is usable (`USABLE`) or not (`UNUSABLE`)
`TABLESPACE_NAME`	`VARCHAR2(30)`	Name of the tablespace containing the partition
`PCT_FREE`	`NUMBER`	Minimum percentage of free space in a block
`INI_TRANS`	`NUMBER`	Initial number of transactions
`MAX_TRANS`	`NUMBER`	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`	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(7)`	Indicates whether or not changes to the index are logged: `NONE` - Not specified See Also: the `*_IND_SUBPARTITIONS` view `YES` `NO`
`COMPRESSION`	`VARCHAR2(13)`	Indicates whether index compression is enabled or disabled for a partitioned index; NULL for a nonpartitioned index: `NONE` - Not specified See Also: the `*_IND_SUBPARTITIONS` view `ENABLED` `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 partition
`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 returned
`SAMPLE_SIZE`	`NUMBER`	Sample size used in analyzing this partition
`LAST_ANALYZED`	`DATE`	Date on which this partition was most recently analyzed
`BUFFER_POOL`	`VARCHAR2(7)`	Actual buffer pool for the partition: `DEFAULT` `KEEP` `RECYCLE` NULL
`FLASH_CACHE`	`VARCHAR2(7)`	Database Smart Flash Cache hint to be used for partition blocks: `DEFAULT` `KEEP` `NONE` Solaris and Oracle Linux functionality only.
`CELL_FLASH_CACHE`	`VARCHAR2(7)`	Cell flash cache hint to be used for partition 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`)
`PCT_DIRECT_ACCESS`	`NUMBER`	If a secondary index on index-organized table, the percentage of rows with `VALID` guess
`GLOBAL_STATS`	`VARCHAR2(3)`	`GLOBAL_STATS` will be `YES` if statistics have been gathered or `NO` if statistics have been aggregated from subpartitions or have not been gathered
`DOMIDX_OPSTATUS`	`VARCHAR2(6)`	Status of the operation on a domain index: NULL - Index is not a domain index `VALID` - Operation performed without errors `FAILED` - Operation failed with an error
`PARAMETERS`	`VARCHAR2(1000)`	For a domain index, the parameter string
`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 partition segment has been created (`YES`) or not (`NO`); `N/A` indicates that this index is subpartitioned and no segment exists at the partition level
`ORPHANED_ENTRIES`	`VARCHAR2(3)`	Indicates whether a global index partition contains stale entries because of deferred index maintenance during `DROP`/`TRUNCATE PARTITION`, or `MODIFY PARTITION INDEXING OFF` operations. Possible values: `YES` - the index partition contains orphaned entries `NO` - the index partition does not contain orphaned entries