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.
- 1 See Indexing a BSON field.
| 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.
- 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.
| 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.