Index-Key Specification

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > CREATE INDEX >

Index-Key Specification

Use the Index-Key Specification of the CREATE INDEX statement to define the key value for the index, to specify whether to sort the index in ascending or descending order, and to identify a default operator class if the secondary access method in the USING clause has no default operator class, or to override its default operator class.

Index-Key Specification:
 
      .-,----------------------------------------------------------------------.
      V                                                               .-ASC--. |
|--(----+-column------------------------------+--+-----------------+--+-DESC-+-+--)--|
        |                     .-,--------.    |  |  (1)            |
        |  (1)                V          |    |  '--------op_class-'
        '--------function--(----func_col-+--)-'

Notes:

Dynamic Server only

Element	Description	Restrictions	Syntax
column	Column or columns used as a key to this index	See Using a Column or Column List as the Index Key.	Identifier
function	User-defined function 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.	Database Object Name
func_col	Columns whose values are arguments to function	See Using a Function as an Index Key (IDS).	Identifier
op_class	Operator class associated with column or function for this index	If the secondary-access method in the USING clause has no default operator class, you must specify one here. (See Using an Operator Class (IDS).)	Identifier

The index-key value can be one or more columns that contain 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.

In Dynamic Server, 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 that contain user-defined data types
One or more values that a user-defined function returns (referred to as a functional index)
A combination of columns and 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 4. 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