Purpose
The USER_INDEXES view displays the indexes on all tables owned by the user.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Related views
ALL_INDEXES
DBA_INDEXES
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| INDEX_NAME | VARCHAR2(128) | NO | The name of the index. |
| INDEX_TYPE | VARCHAR2(27) | NO | The type of the index. |
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the indexed object. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the indexed object. |
| TABLE_TYPE | CHAR(5) | NO | The type of the indexed object. |
| UNIQUENESS | VARCHAR2(9) | NO | Indicates whether the index is unique. |
| COMPRESSION | VARCHAR2(13) | NO | Indicates whether the index is compressed. |
| PREFIX_LENGTH | NUMBER | NO | The number of columns in the prefix of the compressed index. At present, this column is not supported and is NULL by default. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace containing the index. |
| INI_TRANS | NUMBER | NO | The initial number of transactions. At present, this column is not supported and is NULL by default. |
| MAX_TRANS | NUMBER | NO | The maximum number of transactions. At present, this column is not supported and is NULL by default. |
| INITIAL_EXTENT | NUMBER | NO | The size of the initial extent. At present, this column is not supported and is NULL by default. |
| NEXT_EXTENT | NUMBER | NO | The size of secondary extents. At present, this column is not supported and is NULL by default. |
| MIN_EXTENTS | NUMBER | NO | The minimum number of extents allowed in the segment. At present, this column is not supported and is NULL by default. |
| MAX_EXTENTS | NUMBER | NO | The maximum number of extents allowed in the segment. At present, this column is not supported and is NULL by default. |
| PCT_INCREASE | NUMBER | NO | The percentage increase in extent size. At present, this column is not supported and is NULL by default. |
| PCT_THRESHOLD | NUMBER | NO | The percentage of block space allowed for each index. At present, this column is not supported and is NULL by default. |
| INCLUDE_COLUMN | NUMBER | NO | The column ID of the last column in the primary key of the index-organized table. At present, this column is not supported and is NULL by default. |
| FREELISTS | NUMBER | NO | The number of process freelists allocated to each segment. At present, this column is not supported and is NULL by default. |
| FREELIST_GROUPS | NUMBER | NO | The number of freelist groups allocated to each segment. At present, this column is not supported and is NULL by default. |
| PCT_FREE | NUMBER | NO | The minimum percentage of free space in a block. At present, this column is not supported and is NULL by default. |
| LOGGING | VARCHAR2(3) | NO | Indicates whether changes to the index are logged. At present, this column is not supported and is NULL by default. |
| BLEVEL | NUMBER | NO | The B*-tree level. At present, this column is not supported and is NULL by default. |
| LEAF_BLOCKS | NUMBER | NO | The number of leaf blocks in the index. At present, this column is not supported and is NULL by default. |
| DISTINCT_KEYS | NUMBER | NO | The number of distinct indexed values. At present, this column is not supported and is NULL by default. |
| AVG_LEAF_BLOCKS_PER_KEY | NUMBER | NO | The average number of leaf blocks in which each distinct value in the index appears. The value is rounded to the nearest integer. The value is always 1 for indexes that enforce the UNIQUE and PRIMARY KEY constraints. At present, this column is not supported and is NULL by default. |
| AVG_DATA_BLOCKS_PER_KEY | NUMBER | NO | The average number of data blocks in the table that are pointed to by different values in the index that are rounded to the nearest integer. This statistical data is the average number of data blocks containing rows that have a specified value for the indexed columns. At present, this column is not supported and is NULL by default. |
| CLUSTERING_FACTOR | NUMBER | NO | The number of rows in the table based on the value of the index. If the value is close to the number of blocks, the table is well ordered. In this case, the index entries in a single leaf block usually point to rows in the same data block. If the value is close to the number of rows, the table is randomly ordered. In this case, index entries in the same leaf block are unlikely to point to rows in the same data block. At present, this column is not supported and is NULL by default. |
| STATUS | VARCHAR2(8) | NO | Indicates whether a nonpartitioned index is VALID or UNUSABLE. |
| NUM_ROWS | NUMBER | NO | The number of rows in the index. |
| SAMPLE_SIZE | NUMBER | NO | The size of the sample used to analyze the index. At present, this column is not supported and is NULL by default. |
| LAST_ANALYZED | DATE | NO | The most recent date on which the index was analyzed. At present, this column is not supported and is NULL by default. |
| DEGREE | VARCHAR2(40) | NO | The number of threads of each instance that are used to scan the index. At present, this column is not supported and is NULL by default. |
| INSTANCES | VARCHAR2(40) | NO | The number of instances for which the indexes are to be scanned. At present, this column is not supported and is NULL by default. |
| PARTITIONED | VARCHAR2(3) | NO | Indicates whether the index is partitioned. |
| TEMPORARY | VARCHAR2(1) | NO | Indicates whether the index is on a temporary table. At present, this column is not supported and is NULL by default. |
| GENERATED | VARCHAR2(1) | NO | Indicates whether the name of the index is system-generated. At present, this column is not supported and is NULL by default. |
| SECONDARY | VARCHAR2(1) | NO | Indicates whether the index is a secondary object. At present, this column is not supported and is NULL by default. |
| BUFFER_POOL | VARCHAR2(7) | NO | The name of the default buffer pool for index blocks. At present, this column is not supported and is NULL by default. |
| FLASH_CACHE | VARCHAR2(7) | NO | The database smart flash cache for index blocks. At present, this column is not supported and is NULL by default. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | The cell flash cache for index blocks. At present, this column is not supported and is NULL by default. |
| USER_STATS | VARCHAR2(3) | NO | Indicates whether the statistical information was directly entered by the user. At present, this column is not supported and is NULL by default. |
| DURATION | VARCHAR2(15) | NO | The duration of a temporary table. At present, this column is not supported and is NULL by default. |
| PCT_DIRECT_ACCESS | NUMBER | NO | If the index is a secondary index on an index-organized table, the value is the percentage of rows with valid guesses. At present, this column is not supported and is NULL by default. |
| ITYP_OWNER | VARCHAR2(128) | NO | The owner of the index type for a domain index. At present, this column is not supported and is NULL by default. |
| ITYP_NAME | VARCHAR2(128) | NO | The name of the index type for a domain index. At present, this column is not supported and is NULL by default. |
| PARAMETERS | VARCHAR2(1000) | NO | The parameter string for a domain index. At present, this column is not supported and is NULL by default. |
| GLOBAL_STATS | VARCHAR2(3) | NO | Indicates whether statistics of a partitioned index were collected by analyzing the index as a whole or were estimated from statistics on underlying index partitions and subpartitions. At present, this column is not supported and is NULL by default. |
| DOMIDX_STATUS | VARCHAR2(12) | NO | The status of a domain index. At present, this column is not supported and is NULL by default. |
| DOMIDX_OPSTATUS | VARCHAR2(6) | NO | The status of the operation on a domain index. At present, this column is not supported and is NULL by default. |
| FUNCIDX_STATUS | VARCHAR2(8) | NO | The status of a function-based index. |
| JOIN_INDEX | VARCHAR2(3) | NO | Indicates whether the index is a join index. |
| IOT_REDUNDANT_PKEY_ELIM | VARCHAR2(3) | NO | Indicates whether redundant primary key columns are removed from secondary indexes of index-organized tables. At present, this column is not supported and is NULL by default. |
| DROPPED | VARCHAR2(3) | NO | Indicates whether the index has been dropped and moved to the recycle bin. |
| VISIBILITY | VARCHAR2(9) | NO | Indicates whether the index is visible to the optimizer. |
| DOMIDX_MANAGEMENT | VARCHAR2(14) | NO | Indicates whether the domain index is system-managed or user-managed. At present, this column is not supported and is NULL by default. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | Indicates whether the index segment has been created. At present, this column is not supported and is NULL by default. |
| ORPHANED_ENTRIES | VARCHAR2(3) | NO | Indicates whether the global index partition contains obsolete entries because index maintenance is delayed by the DROP PARTITION, TRUNCATE PARTITION, or MODIFY PARTITION INDEXING OFF operation. Valid values:
|
| INDEXING | VARCHAR2(7) | NO | Indicates whether the global index is separated from the base table. Valid values:
|
| AUTO | VARCHAR2(3) | NO | Indicates whether the index is an automatic index. Valid values:
|