Using VoltDB

contents

CREATE INDEX

CREATE INDEX — Creates an index for faster access to a table.

Synopsis

CREATE [UNIQUE] INDEX index-name ON table-name ( index-column [,...])

Description

Creating an index on a table makes read access to the table faster when using the index as a key. Note that VoltDB creates an index automatically when you specify a primary key in the CREATE TABLE statement.

When you specify that the index is UNIQUE, VoltDB constrains the table to at most one row for each index value. If an INSERT or UPDATE statement attempts to create a row where all the index column values match an existing indexed row, the statement fails. Because the uniqueness constraint is applied to the current partition, to ensure global uniqueness for partitioned tables a unique index must contain the partitioning column for the table.

The indexed items (index-column) are either columns of the specified table or numeric expressions, including functions, based on the table. For example, the following statements index a table based on the calculated area and its distance from a set location:

CREATE INDEX areaofplot ON plot (width * height);
CREATE INDEX distancefrom49 ON plot ( ABS(latitude - 49) );

By default, VoltDB creates a tree index. Tree indexes provide the best general performance for a wide range of operations, including exact value matches and queries involving a range of values, such as SELECT ... WHERE Score > 1 AND Score < 10.

If an index is used exclusively for exact matches (such as SELECT ... WHERE MyHashColumn = 123), it is possible to create a hash index instead. To create a hash index, include the string "hash" as part of the index name.

contents