CREATE INDEX — define a new index


CREATE [ UNIQUE ] INDEX index_name ON [schema.]table_name(column_name[, ...] ) 
    [ WITH ( [RESOLUTION n],[MAX KEY SIZE n] ) ]

In addition, the syntax supports multiple CREATE INDEX statements concatenated together, separated by a comma (,). See Improving Index Creation Speed below.


CREATE INDEX constructs an index index_name on the specified table_name. Indexes are primarily used to enhance database performance (though inappropriate use can result in slower performance). NuoDB uses the B-tree index method. This method supports multicolumn indexes (see Example 1). By default, indexes are not UNIQUE.
Use DROP INDEX to remove an index. (See DROP INDEX.)
It is not possible to create a redundant index, that is, an index on a column or set of columns for which an index already exists.

Caution: If DDL statements are to be executed concurrently with DML statements, ensure that a LOCK TABLE command is successfully executed prior to executing the DDL statement.

Note: If you need to create an index with concurrent DML, you must use table locks. For more information on lock functionality, see LOCK.


Improving Index Creation Speed

Creating multiple indexes on the same table involves a table scan for each index created. Concatenating these CREATE INDEX statements, separated each by a comma (,) indicates to NuoDB that only one table scan is required and performance time for the creation of the index will be improved. For example, where one might run the following commands:

CREATE INDEX idxa ON tab(a);
CREATE INDEX idxb ON tab(b);
CREATE INDEX idxc ON tab(c);

Running the statements below will result in faster index creation:

CREATE INDEX idxa ON tab(a),
CREATE INDEX idxb ON tab(b),
CREATE INDEX idxc ON tab(c);

Note the comma instead of semicolon. Concatenation of CREATE INDEX statements in this manner is only supported for one table at a time.


The examples below make use of tables in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.