This is documentation for a previous version of ClustrixDB. Documentation for the latest version can be found here
An invisible index is an index that is maintained by the database but ignored by the planner unless explicitly specified.
Identifying an index as INVISIBLE allows the user to evaluate the impact of:
Dropping an existing index before actually doing so.
Evaluating a new index without impacting existing queries.
Dropping and re-adding an index can take substantial time for large tables, whereas marking an index as VISIBLE or INVISIBLE is a fast, in-place operation that can be easily undone if you do not achieve the expected results.
INVISIBLE indexes are ignored by Sierra, ClustrixDB’s query planner, but continue to be maintained such that they are useable when explicitly named or made VISIBLE.
Unique INVISIBLE indexes still prevent duplicate keys.
To control the visibility for an index, set the VISIBLE or INVISIBLE keyword when defining an index. Indexes are VISIBLE by default.
CREATE TABLE table_name ... INDEX index_name (column_name [,column_name...]) [INVISIBLE | VISIBLE] |
CREATE INDEX index_name ON table_name (column_name [,column_name...]) [INVISIBLE | VISIBLE] |
ALTER TABLE table_name … ALTER INDEX index_name [INVISIBLE | VISIBLE] |
Steps for evaluating an INVISIBLE index vary depending on whether or not the index already exists.
ALTER the index to be INVISIBLE then evaluate the impact:
DROP the index or ALTER it to be VISIBLE, depending on the results. Continue to monitor the query.log.
CREATE a new index as INVISIBLE.
DROP the index or ALTER it to be VISIBLE, depending on the results. Continue to monitor the query.log.