Note
This view is available starting with V2.2.30.
Purpose
This view displays information about all index partitions that users can access in the database.
Applicability
This view is available only in Oracle mode of OceanBase Database.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| INDEX_OWNER | VARCHAR2(128) | NO | The owner of the index. |
| INDEX_NAME | VARCHAR2(128) | NO | The name of the index. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the partition. |
| SUBPARTITION_NAME | VARCHAR2(128) | NO | The name of the subpartition. |
| HIGH_VALUE | VARCHAR2(1024) | NO | The expression bound to the subpartition value. |
| HIGH_VALUE_LENGTH | NUMBER | NO | The length of the expression bound to the subpartition value. |
| PARTITION_POSITION | NUMBER | NO | The position of the partition in the index. |
| SUBPARTITION_POSITION | NUMBER | NO | The position of the subpartition in the partition. |
| STATUS | VARCHAR2(8) | NO | Indicates whether the index partition is available. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace containing the partition. |
| PCT_FREE | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| INI_TRANS | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| MAX_TRANS | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| INITIAL_EXTENT | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| NEXT_EXTENT | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| MIN_EXTENT | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| MAX_EXTENT | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| MAX_SIZE | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| PCT_INCREASE | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| FREELISTS | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| FREELIST_GROUPS | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| LOGGING | VARCHAR2(7) | NO | This column is not supported and is currently displayed as NULL. |
| COMPRESSION | VARCHAR2(8) | NO | Indicates whether the partitioned index is compressed: |
| BLEVEL | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| LEAF_BLOCKS | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| DISTINCT_KEYS | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| AVG_LEAF_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| AVG_DATA_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| CLUSTERING_FACTOR | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| NUM_ROWS | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| SAMPLE_SIZE | NUMBER | NO | This column is not supported and is currently displayed as NULL. |
| LAST_ANALYZED | DATE | NO | This column is not supported and is currently displayed as NULL. |
| BUFFER_POOL | VARCHAR2(7) | NO | This column is not supported and is currently displayed as NULL. |
| FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported and is currently displayed as NULL. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported and is currently displayed as NULL. |
| USER_STATS | VARCHAR2(3) | NO | This column is not supported. It is displayed as NULL. |
| GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported. It is displayed as NULL. |
| INTERVAL | VARCHAR2(3) | NO | This column is not supported. It is displayed as NULL. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported. It is displayed as NULL |
| PARAMETERS | VARCHAR2(1000) | NO | This column is not supported. It is displayed as NULL. |
| DOMIDX_OPSTATUS | VARCHAR2(6) | NO | This column is not supported. It is displayed as NULL. |
Sample query
Query all index subpartitions (subpartitions) that are accessible to the current user.
obclient [SYS]> SELECT INDEX_OWNER, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, SUBPARTITION_POSITION FROM ALL_IND_SUBPARTITIONS;
The query result is as follows:
+-------------+----------------+----------------+-------------------+------------+--------------------+-----------------------+
| INDEX_OWNER | INDEX_NAME | PARTITION_NAME | SUBPARTITION_NAME | HIGH_VALUE | PARTITION_POSITION | SUBPARTITION_POSITION |
+-------------+----------------+----------------+-------------------+------------+--------------------+-----------------------+
| SYS | TBL2_F_RL_IDX1 | P0 | SP0 | 1,3 | 1 | 1 |
| SYS | TBL2_F_RL_IDX1 | P0 | SP1 | 4,6 | 1 | 2 |
| SYS | TBL2_F_RL_IDX1 | P0 | SP2 | 7,9 | 1 | 3 |
| SYS | TBL2_F_RL_IDX1 | P1 | SP3 | 1,3 | 2 | 1 |
| SYS | TBL2_F_RL_IDX1 | P1 | SP4 | 4,6 | 2 | 2 |
| SYS | TBL2_F_RL_IDX1 | P1 | SP5 | 7,9 | 2 | 3 |
+-------------+----------------+----------------+-------------------+------------+--------------------+-----------------------+
6 rows in set
References
Query all index subpartitions (subpartitions) in the current tenant: DBA_IND_SUBPARTITIONS
Query all index subpartitions (subpartitions) owned by the current user: USER_IND_SUBPARTITIONS