How to get corrupted index names only on large tables
Hello Community,
What is the most efficient, memory-safe way to get the names of the corrupted indexes on very large tables for a rebuild. However, if an index has millions of corrupted rows, the .errors array in %ValidateIndices grows too large and throws a errorerror.
Comments
You can validate indexes with either $SYSTEM.OBJ.ValidateIndices() or %Library.Storage.%ValidateIndices(). Both methods check index integrity and can optionally correct problems. They can validate a specified %List of index names or all indexes for the table. [1][2]
For rebuilding, %BuildIndices() is the preferred method rather than %ValidateIndices(), because %ValidateIndices() is significantly slower. %BuildIndices() can rebuild all indexes or a specified list of indexes. [1][2][3][4]
If your goal is just to rebuild known corrupt indexes, the available material confirms that %BuildIndices() accepts a $ListBuild() of index names, for example:
SET status = ##class(myschema.mytable).%BuildIndices($ListBuild("NameIDX","SpouseIDX"))
The available documents do not describe a memory-safe way to return only the names of corrupted indexes without accumulating row-level errors in the %ValidateIndices error array. They describe validation behavior and rebuilding options, but do not provide a documented mechanism for “index names only” output in the situation you described. [1][2][3][4]
You may also use $SYSTEM.OBJ.ValidateIndices() if you want validation to include collection child tables for the table; %Library.Storage.%ValidateIndices() requires separate calls for collection child table indexes. [1][2]
Sources: