The index statements in the SQL standard are recognized, but their implementation is different from competitive products. MonetDB interprets these statements as an advice and often freely neglects it, relying on its own information, access patterns and decision to create and maintain internal indexes for fast access.
index_def:
CREATE [ UNIQUE ] INDEX indexname ON [ schema name . ] table name '(' column name [ ','... ] ')'
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 is accepted (for ease of migration) but it is not enforced or stored.
To enforce uniqueness on a set of columns you must create a unique constraint. E.g.:ALTER TABLE schm.tbl ADD CONSTRAINT tbl_uc1 UNIQUE (c1, c2)
See also ALTER TABLE ADD CONSTRAINT.
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.
MonetDB supports two special kinds of secondary indices: IMPRINTS and ORDERED INDEX. These index types are experimental and have some limitations:
index_def:
CREATE IMPRINTS INDEX indexname ON [ schema name . ] table name '(' column name ')'
There are two kinds of imprints based on column datat 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.
An imprints index is removed using the ordinary DROP INDEX statement. There is
no DROP IMPRINTS INDEX statement.
An imprints index is not maintained automatically and will become inactive after inserts, deletes or updates are done on the column data.
index_def:
CREATE ORDERED INDEX indexname ON [ schema name . ] table name '(' column name ')'
Creates an ordered index on the given column. It is used in various places in the kernel to speed up queries.
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.
An ordered index is removed using the ordinary DROP INDEX statement. There is
no DROP ORDERED INDEX statement.
An ordered 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