Skip to main content

Index definitions

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 [ ','... ] ')'

See also: CREATE INDEX command

 

MonetDB supports two special kinds of secondary indices: IMPRINTS and ORDERED INDEX, introduced in release Dec2016 (v11.25.3). 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. As of release Mar2018 (11.29.3) the ORDERED INDEX does not have this retriction anymore. 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.

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.

See also: CREATE IMPRINTS INDEX command and MonetDBInternals/MALModules/Imprints

index_def:
    CREATE ORDERED INDEX ident ON [ schema name . ] table name '(' column name ')'

Creates a new ordered index on one column of a specific table. This index is a special single column index where the values are stored in ascending order. It can speed-up queries which have a column range or point selection condition (such as: AGE BETWEEN 18 AND 30) or need sorting such as when used in a GROUP BY clause.

See also: CREATE ORDERED INDEX command