What does incomplete index mean

{question}

When checking all indexes on a table, I can see an index flagged as "Incomplete". What does incomplete index mean?

{question}

{answer}

Starting with version 4.0, NuoDB supports online index creation. Indexes created using that method may show up in “Incomplete” state.

What are incomplete indexes?

If an index is created using the CREATE INDEX ... ONLINE command, it is built in multiple stages in a way that allows concurrent updates to the table to proceed without being blocked. The index is first created in the Incomplete state (Incomplete appears before the index name in the output of the SHOW TABLE command) and transitions to a fully-functional state once it is fully created and made available for subsequent queries to use.

When can indexes be incomplete?

The indexes are in a state of "incomplete" while they are being created.

If online index creation fails for some reason, the index will remain that state. Reasons for failing to build the index include attempting to create a unique index over a table that contains duplicate values or the failure of the TE or the SM during index creation. 

Checking for incomplete indexes

To check for the presence of incomplete indexes in a given table, use the SHOW command:

SQL> select * from system.indexes where flags = & 4 > 0;

 INDEXNAME  TABLENAME  SCHEMA  INDEXTYPE  FIELDCOUNT  INDEXID  FLAGS  HISTOGRAMRESOLUTION                    IDENTIFIER
 ---------- ---------- ------- ---------- ----------- -------- ------ -------------------- -----------------------------------------------

 IDX_ONLINE     T1      USER       2           1        167      4            256          IDX_ONLINE-7c0c7f1d-00b1-2849-f6a6-de96f6331597
 

SQL2> show table t1 Tables named T1 Found table T1 in schema USER Fields: F1 string Incomplete Secondary Index: IDX_ONLINE on field: F1

For more information about the flags column, please check INDEXES.

 

Dealing with incomplete indexes

The presence of an incomplete index does not affect read queries, but DML operations continue to update it and a failure may occur if the constraint checks based on the index are violated, for example, a unique constraint violation.

An incomplete index should be dropped manually using DROP INDEX and then recreated using the original CREATE INDEX ONLINE statement. For more information about online index creation, please check CREATE INDEX.

Index creation is a memory-intensive operation. Users could enable spill-to-disk feature so that the memory pressure is reduced. The feature is enabled by default on SM engines where indexes on non-temporary tables are built. For more information, please check Spill To Disk.

If you have any questions, please reach out by clicking here

{answer}

Have more questions? Submit a request

Comments