Note
This view is available starting with V2.2.30.
Purpose
This view displays information about all index subpartitions that users can access in the database.
Applicability
This view is available only in OceanBase Database in Oracle-compatible mode.
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 of the value bound to the subpartition. |
| HIGH_VALUE_LENGTH | NUMBER | NO | The length of the expression of the value bound to the subpartition. |
| 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. It is displayed as NULL. |
| MAX_SIZE | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| PCT_INCREASE | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| FREELISTS | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| FREELIST_GROUPS | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| LOGGING | VARCHAR2(7) | NO | This column is not supported. It is displayed as NULL. |
| COMPRESSION | VARCHAR2(8) | NO | Indicates whether the partition index is compressed:
|
| BLEVEL | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| LEAF_BLOCKS | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| DISTINCT_KEYS | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| AVG_LEAF_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| AVG_DATA_BLOCKS_PER_KEY | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| CLUSTERING_FACTOR | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| NUM_ROWS | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| SAMPLE_SIZE | NUMBER | NO | This column is not supported. It is displayed as NULL. |
| LAST_ANALYZED | DATE | NO | This column is not supported. It is displayed as NULL. |
| BUFFER_POOL | VARCHAR2(7) | NO | This column is not supported. It is displayed as NULL. |
| FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported. It is displayed as NULL. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | This column is not supported and is displayed as NULL. |
| USER_STATS | VARCHAR2(3) | NO | This column is not supported and is displayed as NULL. |
| GLOBAL_STATS | VARCHAR2(3) | NO | This column is not supported and is displayed as NULL. |
| INTERVAL | VARCHAR2(3) | NO | This column is not supported and is displayed as NULL. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported and is displayed as NULL. |
| PARAMETERS | VARCHAR2(1000) | NO | This column is not supported and is displayed as NULL. |
| DOMIDX_OPSTATUS | VARCHAR2(6) | NO | This column is not supported and is displayed as NULL. |
Sample query
Query the information about all index subpartitions (subpartitions) in the databases that the user can access.
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 the information about all index subpartitions (subpartitions) in the current tenant: DBA_IND_SUBPARTITIONS
Query the information about all index subpartitions (subpartitions) that the current user owns: USER_IND_SUBPARTITIONS