1.1

CREATE GLOBAL HASH INDEX fl_idx ON FLIGHTS (flight_id,
segment_number);
CREATE INDEX
Creates an index on one or more columns of a table.
Syntax
CREATE [ UNIQUE ] INDEX index_name
ON table-name (
column-name [ ASC | DESC ]
[ , column-name [ ASC | DESC ] ] * ) [ -- SQLFIRE-PROPERTIES caseSensitive
= { false | true} ]
Description
The CREATE INDEX statement creates an index on one or more columns of a table. Indexes can speed up
queries that use those columns for filtering data, or can also enforce a unique constraint on the indexed columns.
The maximum number of columns for an index key in SQLFire is 16. An index name cannot exceed 128
characters. A column must not be named more than once in a single CREATE INDEX statement. Different
indexes can name the same column, however.
SQLFire does not support creating an index on a column of datatype BLOB, CLOB, or LONG VARCHAR FOR
BIT DATA. Indexes are supported for LONG VARCHAR columns.
SQLFire can use indexes to improve the performance of data manipulation statements. In addition, UNIQUE
indexes provide a form of data integrity checking. However, the UNIQUE constraint only applies to the local
member's data and not globally in the whole table. To enforce a unique index globally for a partitioned table,
use the CREATE GLOBAL HASH INDEX on page 471 statement.
Index names are unique within a schema. Some database systems allow different tables in a single schema to
have indexes of the same name, but SQLFire does not. Both index and table are assumed to be in the same
schema if a schema name is specified for one of the names, but not the other. If schema names are specified for
both index and table, an exception will be thrown if the schema names are not the same. If no schema name is
specified for either table or index, the current schema is used.
By default, SQLFire uses the ascending order of each column to create the index. Specifying ASC after the
column name does not alter the default behavior. The DESC keyword after the column name causes SQLFire
to use descending order for the column to create the index. Using the descending order for a column can help
improve the performance of queries that require the results in mixed sort order or descending order and for
queries that select the minimum or maximum value of an indexed column.
Partial Index Lookups
When a query references a subset of columns in an index, SQLFire only uses the index for those columns that
form a prefix of the indexed columns. For example, consider the index:
CREATE INDEX idx ON mytable (col1, col2, col3);
SQLFire can use the above index only for search conditions on:
• col1 only, or
• col1 and col2, or
• col1, col2, and col3.
vFabric SQLFire User's Guide472
vFabric SQLFire Reference