CREATE INDEX Statement

CREATE INDEX defines a secondary indexes for a table or materialized window. 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 | materialized_window_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.

materialized_window_identifier

The unique identifier (name) for a materialized window.

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

Back to Top ^