Note
This view is available starting with V2.2.30.
Purpose
This view displays information about all index subpartitions that are accessible to users in the database.
Applicability
This view is available only in OceanBase Database in Oracle 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 that contains 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 reserved. It currently shows as NULL. |
| USER_STATS | VARCHAR2(3) | NO | The USER_STATS column is not supported and will be displayed as NULL. |
| GLOBAL_STATS | VARCHAR2(3) | NO | This field is not supported. It will be displayed as NULL in current versions. |
| INTERVAL | VARCHAR2(3) | NO | This column is not supported and defaults to NO. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | This column is not supported and currently displays NULL values. |
| PARAMETERS | VARCHAR2(1000) | NO | This column is not supported and is currently NULL. |
| DOMIDX_OPSTATUS | VARCHAR2(6) | NO | The value is always NULL. |
Sample query
Query 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 all index subpartitions (subpartitions) in the current tenant: DBA_IND_SUBPARTITIONS
Query all index subpartitions (subpartitions) owned by the current user: USER_IND_SUBPARTITIONS