- Index

< Previous |  | Next >

# CREATE INDEX Statement

CREATE INDEX defines a secondary indexes for a table. The index can be based on one or more fields in the associated schema, other than the primary key. By default, btree ordering of index values is used; you can optionally use hash indexing instead.

## Syntax

    CREATE INDEX index_identifier ON {table_identifier}
      [USING {HASH | BTREE}] (
        field_identifier[,...]
      );

### Substitutable Fields

`index_identifier`

  A unique identifier (name) for the index.

`table_identifier`

  The unique identifier (name) for a table.

`field_identifier`

  A field (other than the primary key) in the schema that is associated with the table. If the field references a named schema, the entire schema is used as the key.

## Discussion

The following example defines a table with a primary key, followed by a CREATE INDEX statement. The named schema referenced in the table schema is not shown:

    CREATE MEMORY TABLE Symbols3
      (ss SymbolSchema, Quantity int)
      PRIMARY KEY(Quantity) ;
    CREATE INDEX i1 ON Symbols3 USING BTREE (ss);

In this example we used the entire named schema as the secondary key: all of the named schema's fields are used, in sort order. As with primary keys (described in CREATE TABLE Statement), we cannot designate an individual field in the named schema as the primary key. However, if our example had used `ss` as the primary key, we could have used the non-hierarchical field (Quantity) as the secondary key.

## Related Topics

- CREATE TABLE

Back to Top ^