SQL Create Index

An SQL index, which corresponds to an underlying eXtremeDB index, can be created to improve lookup performance for specific queries. (Please see SQL Optimizer for details about performance optimization using indexes.) Indexes are created using the SQL create index statement specifying the table and key field(s) For example:

 
    create table t (x integer);
    create index tx on t(x);
            
     

Here a B-Tree (tree) index (the default type) is created on column (field) x of table t.

The syntax is as follows:

 
    CREATE [UNIQUE] INDEX name ON table "(" key { "," key } ")" 
        [USING (HASH | RTREE | PTREE | TRIGRAM | INCLUSIVE)]
        [IF NOT EXISTS]
     
    key : column_name [ asc | desc ]
     

Note that all SQL keywords are case insensitive in eXtremeSQL - i.e. CREATE INDEX and create index are equivalent.

The keywords HASH, RTREE, PTREE, and TRIGRAM refer to the eXtremeDB indexes of type hash (hash table), rtree (spatial search), trie (Patricia Trie), trigram (Trigram search) respectively. The keyword INCLUSIVE indicates that the index is a key-value-inclusive index.

A key specification can include the keywords ASC or DESC to indicate whether the index sorts in ascending or descending order. And note that compound indexes can be created by specifying a comma-delimited list of keys.

If Not Exists

The if not exists clause can be used to override an error when the index already exists. For example, note how the following create index statement fails while adding the if not exists clause allows the execution to succeed:

 
    XSQL>create table foo(x integer);
    XSQL>insert into foo values (1);
    XSQL>create index idx on foo(x);
    XSQL>create index idx on foo(x);
    ERROR: Compiler error at position 24: Index idx already defined for table foo
    create index idx on foo(x)
    ^
    XSQL>create index if not exists idx on foo(x);
    XSQL>select * from foo;
    x
    ------------------------------------------------------------------------------
    1
     
    Selected records: 1