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 ')'
There are two kinds of imprints based on column type: numerical and string. On numerical colums it creates a new index that stores metadata (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.
On string columns it creates an index that can speed up LIKE queries by
pre-filtering the strings using a fast but not totally accurate filtering
algorithm. The creation of the string imprint is an expensive operation and in
order to avoid performance degradation in the presence of updates, it can only
be applied to tables marked read only. Use the syntax ALTER TABLE foo SET READ ONLY to mark table foo as read only.
Only one column can be indexed per index. 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 ')'
Creates a new ordered index on the given column. It is used in various places in the kernel to speed up queries.
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