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 their 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
  • PORTER - Porter Stemming algorithm. This tokenizer uses the same rules to separate the input document into terms including folding all terms into lowercase, but also uses the Porter Stemming algorithm to reduce related English language words to a common root. For example, using the same input document as in the paragraph above, the porter tokenizer extracts the following tokens: "right now thei veri frustrat".
    https://tartarus.org/martin/PorterStemmer/
  • ICU - For Unicode encoded strings (e.g., UTF-8)
  • 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) - consistent with MS-SQL "CHANGE_TRAKING default of AUTO
  • IMMEDIATE
  • ATCOMMIT

Example

CREATE FULLTEXT INDEX custmast_fts ON custmast( cm_custname ) TOKENIZER PORTER UPDATE_MODE ASYNC

Query Examples

Query all words from the “document” column from the “docs” table for which "linux" is the first token of at least one column:

SELECT * FROM docs WHERE document MATCH '^linux';

Query all documents for which the first token in column "title" begins with "lin":

SELECT * FROM docs WHERE body MATCH 'title: ^lin*';

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