The index statements in the SQL standard are recognized, but their implementation is different from competitive products. MonetDB/SQL interprets these statements as an advice and often freely neglects it, relying on its own decision to create and maintain indexes for fast access. Also the UNIQUE qualifier is not honored or enforced. To add a uniqueness constraint for a table or column use UNIQUE in the ALTER TABLE ADD CONSTRAINT statement.
index_def:
CREATE [ UNIQUE ] INDEX ident ON [ schema name . ] table name '(' column name [ ','... ] ')'
Note: Although CREATE INDEX commands are accepted by the MonetDB parser for SQL compliance purposes, it currently does not create a physical secondary index via this SQL command. Instead MonetDB internally decides which column search accelerator(s) to create, persist and use during SQL query execution. The index name must be unique within the schema of the table. You can not specify the schema for the index. It will be placed in the same schema as the table.
The UNIQUE keyword does NOT enforce a uniqueness constraint. To create a unique constraint use: ALTER TABLE s.t ADD CONSTRAINT t_uc UNIQUE (c1, c2) instead.
MonetDB supports two special kinds of secondary indices: IMPRINTS and ORDERED INDEX. These index types are experimental and have some limitations: Only 1 column can be indexed per index. Only columns of numeric data type (tinyint, smallint, integer, bigint, hugeint, float, real, double, decimal) can be indexed.
index_def:
CREATE IMPRINTS INDEX ident ON [ schema name . ] table name '(' numeric column name ')'
Creates a new imprints index on one numerical column of a specific table. This index stores meta data (min, max, nulls) on segments of column data. It can speed-up queries which have a column selection condition (such as: AGE IS NULL OR AGE >= 18). It is a very compact index.
Only 1 column can be indexed per index. Only columns of fixed size data type ( so not on: char, varchar, clob, blob, url, json, inet and uuid) can be indexed. The index name must be unique within the schema of the table. You can not specify the schema for the index. It will be placed in the same schema as the table.
The index is not maintained automatically and will become inactive after inserts, deletes or updates are done on the column data. An imprints index is removed using the ordinary DROP INDEX statement. There is no DROP IMPRINTS INDEX statement.
index_def:
CREATE ORDERED INDEX ident ON [ schema name . ] table name '(' column name ')'
The index is NOT maintained automatically and will become inactive when inserts, deletes or updates are done on the column data. Since creating these indices can be expensive, they are only used when explicitly created. They are useful for large static (or read only) tables.
Associated system table: sys.idxs