Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

CREATE INDEX

Description

Creates an index on the specified table using the specified columns of the table. An index improves the performance of FairCom DB SQL operations whose predicates are based on the indexed column. However, an index slows the performance of INSERT, DELETE, and UPDATE operations.

Syntax

CREATE [ FULLTEXT | UNIQUE ] INDEX [ IF NOT EXISTS ] index_name

ON table_name

( {column_name [ASC | DESC]} [, ... ])

[ STORAGE_ATTRIBUTES storage_attributes | fulltext_options ];

storage_attributes ::

'PARTITION=rule' | 'PARTITION'

rule ::

conditional expression referencing columns evaluating to a numeric value.


fulltext_options ::

[ TOKENIZER tokenizer_specs ]

[ MAX_TOKENSIZE number ]

[ UPDATE_MODE upd_option ]

[ TOKENIZER_OPTIONS 'parameters' ]

tokenizer_specs ::

ICU | SIMPLE | PORTER | dllname_for_custom

upd_option ::

ASYNC | IMMEDIATE | ATCOMMIT

Arguments

UNIQUE

A UNIQUE index will not allow the table to contain any rows with duplicate column values for the set of columns specified for that index.

IF NOT EXISTS

This argument avoids failure by creating the index only if an index of the same name does not already exist.

index_name

The name of the index has to be unique for the specified table.

table_name

The name of the table on which the index is being built.

column_name [ , ... ]

The columns on which searches and retrievals will be ordered. These columns are called the index key. When more than one column is specified in the CREATE INDEX statement a concatenated index is created.

Note: Fields of type LVARBINARY and LVARCHAR cannot be included in a SQL index due to size.

ASC | DESC

The index can be ordered as either ascending (ASC) or descending (DESC) on each column of the concatenated index. The default is ASC.

Full-Text Indexes

Similar to a traditional b-tree index over a FairCom DB data file, you may now define a Full-Text Index (FTI) by specifying which character-type fields to include in this search index. An additional set of FTI files will be maintained on disk. An FTI can be actively maintained in “real-time” or as a deferred operation in the background.

TOKENIZER - This is the algorithm used to create the text tokens to be indexed.

  • SIMPLE - uses whitespace and punctuation to delimit case-insensitive terms encoded as ASCII characters.
  • PORTER - Porter Stemming algorithm for the English language treats all words as lowercase and reduces regular nouns and verbs to their root form. For example, the phrase, "Americans love their baseball" is reduced to the following stemmed terms: "american", "love", "their", and "baseball". These stemmed terms match the following words: "american", "americans", "Americans", "aMERIcans", "love", "loves", "loved", "loving", "their", "baseball", "baseballs", "bAsEbAlls", and so forth. It does not match uncommon stemming rules, such as matching "him" and "hr" to "their' or matching "loves" to "love". See https://tartarus.org/martin/PorterStemmer/
  • ICU - uses whitespace and punctuation to delimit case-insensitive terms encoded as Unicode characters.
  • Shared object name of a custom-built tokenizer. Contact FairCom for information regarding a tokenizer SDK.

Default tokenizer:

  • SIMPLE for ASCII server
  • ICU for Unicode server

TOKENIZER_OPTIONS - options to pass into the specified tokenizer algorithm. Generally, this is intended for custom tokenizers.

MAX_TOKENSIZE - The largest tex token to be indexed. Default is 45 characters.

UPDATE_MODE - Three modes are available

ASYNC (default)

  • IMMEDIATE
  • ATCOMMIT

Example

Full-Text Index Restrictions

A full-text search can not be created as a UNIQUE index. Error 20006 is returned.

Columns can not be specified as ASC or DESC in creating a full text index.

A full-text index cannot also be created as a partitioned file index.

Storage Attributes

A quoted string that specifies index attributes that are specific to FairCom DB SQL indexes. FairCom DB SQL interprets the following attribute strings:

  • ''PARTITION=<rule>' - Creates the index as the partition index to the table enabling multiple table partitions. This option can be specified after a table has been created, and the table will be rebuilt according to the partition rule in effect.

    <rule> is a FairCom DB conditional expression evaluating to a numeric value.
  • ''PARTITION' - Creates the index as the partition index to the table enabling multiple table partitions. This option can be specified after a table has been created, and the table will be rebuilt according to the partition rule in effect. (This type of partitioned file currently require a server with your partition rules included at compile time. See the FairCom DB Programmer's Reference Guide for details.)

To combine STORAGE_ATTRIBUTE options, separate them with a semicolon (;).

Example

CREATE UNIQUE INDEX postdate_idx ON customer (invoice_date) STORAGE_ATTRIBUTES 'partition' ;

Authorization

The user executing this statement must have any of the following privileges:

  • DBA privilege.
  • Ownership of the index.
  • INDEX privilege on the table.
  • EXCLUSIVE file access is required for this operation.

SQL Compliance

ODBC Core SQL grammar. Extensions: STORAGE_ATTRIBUTES,

Environment

Embedded SQL, interactive SQL, ODBC applications

Related Statements

CREATE TABLE, DROP INDEX, ALTER TABLE

TOCIndex