Index-key specification

Use the Index-key specification of the CREATE INDEX statement to define the key value for the index. This can also specify the ascending or descending sort order, and the operator class.

This is the syntax of the Index-Key Specification:
(1)
Index-Key Specification

1  ( + ,
2.2.1  column
2.2.1  function ( + , func_col )
2.1?  op_class
2.1  %BSON column specification1
2.1! ASC
2.1? DESC )
Element Description Restrictions Syntax
column Column whose value is used as a key to this index See Restrictions on columns as index keys. Identifier
function User-defined function whose return value is used as a key to this index Must be a nonvariant function that does not return a large object data type. Cannot be a built-in algebraic, exponential, log, or hex function. Identifier
func_col Column whose value is an argument to function Cannot be of a collection data type. See Using the return value of a function as an index key. Identifier
op_class Operator class associated with column or function for this index key If the secondary-access method in the USING clause has no default operator class, you must specify one here. (See Using an Operator Class.) Identifier

The index-key value can be one or more columns of built-in data types. If you specify multiple columns, the concatenation of values from the set of columns is treated as a single composite column for indexing.

The index-key value also can be one of the following:
  • A column of type LVARCHAR(size), if size is smaller than 387 bytes
  • One or more columns of user-defined data types
  • One or more values that a user-defined function returns (referred to as a functional index), where the argument list of the UDF is one or more column values in the same row
  • A combination of one or more column values and the return value from one or more user-defined functions.

The 387-byte LVARCHAR size limit is for dbspaces of the default (2 kilobyte) page size, but dbspaces of larger page sizes can support larger index key sizes, as listed in the following table.

Table 1. Maximum Index Key Size for Selected Page Sizes
Page Size Maximum Index Key Size
2 kilobytes 387 bytes
4 kilobytes 796 bytes
8 kilobytes 1,615 bytes
12 kilobytes 2,435 bytes
16 kilobytes 3,245 bytes

Specifying the sort order

By default, the index is sorted in ascending order, from the lowest value to the highest, according to the collation order for the locale, or else to the collation order that was in effect when the index was created, if the SET COLLATION statement has specified a nondefault collation. You can use the DESC keyword to reverse the sort order, so that the index is sorted from the highest value to the lowest.

If you explicitly specify the ASC keyword in the Index-Key Specification, the index is sorted in ascending order.

Specifying an operator class

If the secondary access method in the USING clause has no default operator class, the Index-Key Specification can specify an operator class for the index key.

If the secondary access method in the USING clause has a default operator class, the Index-Key Specification can specify an operator class to override the default operator class for the index.