CHECK GLOBAL INDEX verifies data consistency between a base table and its global secondary index (GSI) table, and repairs any inconsistencies found.
How it works
The statement checks whether index entries are present, correctly positioned, and match the base table data. Any discrepancies are classified into one of four error types: MISSING, ORPHAN, CONFLICT, or ERROR_SHARD.
Syntax
CHECK GLOBAL INDEX gsi_name [ON tbl_name] [extra_cmd]Parameters
| Parameter | Description |
|---|---|
gsi_name | The name of the GSI to check. |
tbl_name | Optional. The name of the base table. When specified, the statement also validates the index relationship between the GSI table and the base table. |
extra_cmd | Optional. Controls the operation mode. See the table below. |
extra_cmd options
| Value | What it does | When to use |
|---|---|---|
| *(none)* | Checks the GSI and returns any inconsistencies found. | Run this first to identify errors. |
SHOW | Displays the result of the most recent verification or correction for the specified GSI. | Use after a correction to review the repair report. |
CORRECTION_BASED_ON_PRIMARY | Repairs the GSI table based on the base table data. | Use after identifying errors with the default check. |
Usage notes
Verification and correction consume system resources. In most cases, this occurs because data in the base table or index table is locked and corrected in batches during correction operations. Run these operations during off-peak hours.
For large tables, verification can take a significant amount of time. To run it without blocking, use a HINT to enable
PURE_ASYNC_DDL_MODE, which executes the DDL statement in pure asynchronous mode.If a row has multiple types of errors, the result set returns multiple rows with different
ERROR_TYPEvalues for the same primary key.The last row in the result set is always a
SUMMARYrow. Check this row first to determine whether errors were found.
For more information about GSIs, see GSI.
Result columns
| Column | Description |
|---|---|
GSI_TABLE | The name of the GSI. |
ERROR_TYPE | The error type. Valid values: MISSING (missing index entry), ORPHAN (orphan index entry with no matching base table row), CONFLICT (index data does not match the base table), ERROR_SHARD (data shard is in the wrong position), SUMMARY (overall result). |
STATUS | The state of the entry. FOUND means an error was detected. REPAIRED means the error was fixed. -- is used for the SUMMARY row. |
PRIMARY_KEY | The primary key of the affected row. |
DETAILS | The details of the error. |
Examples
The following examples demonstrate the full verify-and-repair workflow.
Check for inconsistencies
Run the following statement to check the GSI g_i_check:
CHECK GLOBAL INDEX `g_i_check`;If no errors are found, the result contains only the SUMMARY row:
+-------------+------------+--------+-------------+-----------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+--------+-------------+-----------------------------+
| `g_i_check` | SUMMARY | -- | -- | OK (7025/7025 rows checked) |
+-------------+------------+--------+-------------+-----------------------------+
1 row in set (1.40 sec)If errors are found, one row is returned per error, followed by the SUMMARY row:
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| `g_i_check` | ORPHAN | FOUND | (100722) | {"GSI":{"id":100722,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_binary":"OTkAAAAAAAAAAA==","c_int_32":271}} |
| `g_i_check` | CONFLICT | FOUND | (108710) | {"Primary":{"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255},"GSI":{"c_int_32_un":123456,"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255}} |
| `g_i_check` | MISSING | FOUND | (100090) | {"Primary":{"id":100090,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_blob_tiny":"YeS4reWbvWE=","c_int_32":280}} |
| `g_i_check` | SUMMARY | -- | -- | 3 error found (7025/7025 rows checked) |
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (1.92 sec)Repair inconsistencies
After identifying errors, run the following statement to repair the GSI based on the base table:
CHECK GLOBAL INDEX g_i_check CORRECTION_BASED_ON_PRIMARY;The following result is returned:
+-------------+------------+--------+-------------+------------------------------------------------------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+--------+-------------+------------------------------------------------------------------------+
| `g_i_check` | SUMMARY | -- | -- | Done. Use SQL: { CHECK GLOBAL INDEX `g_i_check` SHOW; } to get result. |
+-------------+------------+--------+-------------+------------------------------------------------------------------------+
1 row in set (1.40 sec)View the repair report
Run the following statement to view the result of the most recent verification or correction:
CHECK GLOBAL INDEX `g_i_check` SHOW;Each repaired error appears as a row with STATUS set to REPAIRED:
+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| `g_i_check` | MISSING | REPAIRED | (100090) | {"Primary":{"id":100090,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_blob_tiny":"YeS4reWbvWE=","c_int_32":280}} |
| `g_i_check` | CONFLICT | REPAIRED | (108710) | {"Primary":{"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255},"GSI":{"c_int_32_un":123456,"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255}} |
| `g_i_check` | ORPHAN | REPAIRED | (100722) | {"GSI":{"id":100722,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_binary":"OTkAAAAAAAAAAA==","c_int_32":271}} |
| `g_i_check` | SUMMARY | -- | -- | 3 error found (7025/7026 rows checked.) Finish time: 2020-01-13 14:41:51.0 |
+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)