CREATE INDEX

CREATE [UNIQUE|VIRTUAL|BITMAP] INDEX indexname ON tablename ( colname [ASC|DESC] [, colname ...] ) [COMPRESS [prefix_cols]] [TABLESPACE "tablespacename"]

where the table name should match that in the preceding CREATE TABLE statement. The index name should be the same as the table name with a suffix of "_Axx" where xx is the path number (01 to the maximum supported defined by the constant _KDB_MAX_INDEX). The parentheses contain the columns (maximum of _KDB_MAX_KEY_COLS) defining the segments of the key.

Though UNIQUE is optional in ANSI SQL, KDB requires all indices to be unique so this keyword must be used for a KDB table.

The ASC and DESC modifiers are optional and if not present ASC is assumed. However DESC, for descending order, is not supported in KDB.

NULL columns should not be indexed as rows with NULL index columns will not appear in the index unless the index key contains at least one NOT NULL column. Variable length VARCHAR columns, which are NULL if blank, may appear in an index provided this restriction is obeyed.

If the index is marked with the VIRTUAL keyword then the index is called a virtual index. For SQL databases like Oracle, no actual index is created and the columns just define the order for sequential access. In general a virtual index is not unique and random access, with KI_READ_RANDOM say, is not allowed on a virtual index. When executing the first KI_READ_NEXT on a virtual index path, the database will have to collect all the relevant rows and sort them into the order defined by the path. To get good performance at least one column in the index definition should be the leading column in a real index so that this involves an index range scan rather than a full table scan. As a sort is required the number or rows possible in the result set should be limited.

Because no actual index is involved for Oracle, creating and dropping a virtual index is a cheap operation. Furthermore there is no overhead in updating a virtual index associated with inserts, updates and deletes on the table. However for KDB and for cached tables a real index is always created.

An index can have the BITMAP keyword specified. This is ignored for KDB but generates a bitmap index for Oracle that is optimized for data warehouse applications. Because of the expense of creating or modifying this type of index it is not suitable for volatile table in an OLTP environment. There should a much smaller cardinality for the index columns than there are rows in the table thus allowing a much denser index. The index will not be unique and cannot be used for random access. Only include columns with small cardinality and don't add extra columns just to get a specific order within the bitmapped key columns.

The optional COMPRESS clause may be used with databases that support key compression, e.g. Oracle. The optional prefix_cols is the number of leading key columns to be compressed which is assumed to be the number in the key less one if not present. Native KDB does not support key compression.

If a tablespace is specified then the index will be created in that tablespace otherwise it will created in the tables tablespace.

You can choose to index only part of a text column (CHAR, VARCHAR or HEX) by using the SUBSTR() function which takes one or two arguments. The first is the offset into the column and is counted from 1. The optional second argument is the length and if omitted, implies the remainder of the column.

Columns with OCCURS clauses can be indexed either individually using a subscript or in total (CHAR and VARCHAR only).

Examples

CREATE UNIQUE INDEX SL00trans_A1 ON SL00trans (fname, sname, phone(1))
CREATE UNIQUE INDEX SL00trans_A2 ON SL00trans (SUBSTR(sname,1,20))

Compatibility

COMPRESS, VIRTUAL and BITMAP index types were introduced with KCML 6.60. The use of SUBSTR() for indexing parts of text columns came with KCML 6.67.