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 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.

If a query includes search conditions only for col1 and col3, then the index is used only for the col1 search condition, because col1 is a prefix of the indexed columns. The search condition for col3 is treated as a separate, unindexed filter operation.

Create additional indexes when necessary to ensure that queries use a prefix of an index's columns.

Case Sensitivity for Indexes

By default all indexes are case-sensitive. SQLFire supports case-insensitive indexes for CHAR and VARCHAR columns if you specify the caseSensitive=false hint at the end of the CREATE INDEX statement. A case insensitive index enables queries to look up column values while ignoring case differences.

SQLFire performs case insensitive index lookups only for equality-based criteria that appear in queries. Case is not ignored when using the LIKE clause, or when using the >, >=, <, or <= operators.

Note: Use the EXPLAIN command and view the generated query plan to verify that case insensitive index searches are used where needed.

For columns that are not part of an index, SQLFire observes case-sensitivity only if it is explicitly required (for example, if the query specifies UPPER(column-name='UPPERCASE_VALUE').

Queries that use the OR clause to perform comparisons on columns of a case-sensitive index must use the UPPER function on those columns to ensure correct results.

Example

Create an index on two columns:

CREATE INDEX idx ON FLIGHTS (flight_id ASC, segment_number DESC);
Create a case-insensitive index:
CREATE INDEX idx ON FLIGHTS (flight_id ASC, segment_number DESC) -- SQLFIRE-PROPERTIES caseSensitive=false <return>
;