Use SHOW GLOBAL INDEX to view global secondary indexes - PolarDB

Use SHOW GLOBAL INDEX to list all global secondary indexes (GSIs) in the current database, including indexes that are still being created.

Syntax

SHOW GLOBAL {INDEX | INDEXES} [FROM [schema_name.]tbl_name]

The schema_name and tbl_name parameters are optional. Use them to filter results to a specific table or query indexes in a database other than the current one.

Examples:

-- List all GSIs in the current database
SHOW GLOBAL INDEX;

-- List GSIs for a specific table in the current database
SHOW GLOBAL INDEX FROM xxx_tb;

-- List GSIs for a table in a different database (cross-database query)
SHOW GLOBAL INDEX FROM xxx_db.xxx_tb;

Output columns

SHOW GLOBAL INDEX returns one row per GSI. The following table describes each column.

Column	Description
`SCHEMA`	The database name.
`TABLE`	The table name.
`NON_UNIQUE`	Whether the index allows duplicate values. `0` means the index is unique (duplicates not allowed); `1` means it is a common index (duplicates allowed).
`KEY_NAME`	The index name.
`INDEX_NAMES`	The index key columns.
`COVERING_NAMES`	The covering columns included in the index.
`INDEX_TYPE`	The index type. Valid values: `BTREE`, `HASH`, `NULL` (not specified).
`DB_PARTITION_KEY`	The database shard key.
`DB_PARTITION_POLICY`	The database sharding function.
`DB_PARTITION_COUNT`	The number of database shards.
`TB_PARTITION_KEY`	The table shard key.
`TB_PARTITION_POLICY`	The table sharding function.
`TB_PARTITION_COUNT`	The number of table shards.
`STATUS`	The current state of the index. See Index status.

Index status

The STATUS column reflects the build progress of a GSI. The following table lists all possible values.

Status	DDL operations on the host table	Description
`CREATING`	Not allowed	The index is being built.
`DELETE_ONLY`	Not allowed	The index is in an intermediate build state.
`WRITE_ONLY`	Not allowed	The index is in an intermediate build state.
`WRITE_REORG`	Not allowed	The index is in an intermediate build state.
`PUBLIC`	Allowed	The index is fully built and in active use.
`ABSENT`	Allowed	—

While a GSI is in the CREATING, DELETE_ONLY, WRITE_ONLY, or WRITE_REORG state, PolarDB-X blocks DDL operations on the host table to maintain index consistency during the build process. Once the GSI reaches PUBLIC or ABSENT, DDL operations on the host table are allowed again.

Note If you need to run DDL operations on a table whose GSI is still building, wait until the STATUS changes to PUBLIC or ABSENT before proceeding.

Example

The following example shows the output of SHOW GLOBAL INDEX for a database that has two tables with GSIs — one index in PUBLIC state and one still CREATING.

SHOW GLOBAL INDEX;

+---------------------+----------------------+------------+-------------------------------+----------------------------+------------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+----------+
| SCHEMA              | TABLE                | NON_UNIQUE | KEY_NAME                      | INDEX_NAMES                | COVERING_NAMES                           | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS   |
+---------------------+----------------------+------------+-------------------------------+----------------------------+------------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+----------+
| XXXX_DRDS_LOCAL_APP | full_gsi_ddl_renamed | 1          | g_i_c_ddl_c_blob_long_renamed | c_blob_long                | id, c_bit_1, c_bit_8, ...               | NULL       | c_blob_long      | HASH                | 4                  | c_blob_long      | HASH                | 3                  | PUBLIC   |
| XXXX_DRDS_LOCAL_APP | full_gsi_ddl_renamed | 1          | g_i_c_ddl_c_mediumint_1       | c_mediumint_1              | id, c_bit_1, c_bit_8, ...               | NULL       | c_mediumint_1    | HASH                | 4                  | c_mediumint_1    | HASH                | 3                  | PUBLIC   |
| XXXX_DRDS_LOCAL_APP | full_gsi_ddl_renamed | 1          | g_i_c_ddl_c_smallint_16_un    | c_smallint_16_un, c_time_1 | id, c_bit_1, c_bit_8, ...               | NULL       | c_smallint_16_un | HASH                | 4                  | c_smallint_16_un | HASH                | 3                  | PUBLIC   |
| XXXX_DRDS_LOCAL_APP | t_order              | 0          | g_i_seller                    | seller_id                  | id, order_id                             | HASH       | seller_id        | HASH                | 4                  | seller_id        | HASH                | 2                  | CREATING |
+---------------------+----------------------+------------+-------------------------------+----------------------------+------------------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+----------+
4 rows in set (0.01 sec)

The last row shows g_i_seller on the t_order table is still CREATING. DDL operations on t_order are blocked until the index build completes and the status changes to PUBLIC.