Creating Composite Indexes

A simple index lists only one column (or only one function, whose argument list must be a list of one or more columns) in its Index Key Specification. Any other index is a composite index. You should list the columns in a composite index in the order from most frequently used to least frequently used.

If you use SET COLLATION to specify the collating order of a nondefault locale, you can create multiple indexes on the same set of columns, using different collations. (Such indexes are useful only on NCHAR or NVARCHAR columns.)

The following example creates a composite index using the stock_num and manu_code columns of the stock table: 
CREATE UNIQUE INDEX st_man_ix ON stock (stock_num, manu_code);

The UNIQUE keyword prevents any duplicates of a given combination of stock_num and manu_code. The index is in ascending order by default.

You can include up to 16 columns in a composite index. The total width of all indexed columns in a single composite index cannot exceed 380 bytes.

An index key part is either a column in a table, or the result of a user-defined function on one or more columns. A composite index can have up to 16 key parts that are columns, or up to 341 key parts that are values returned by a UDR. This limit is language-dependent and applies to UDRs written in SPL or Java™. Functional indexes based on C language UDRs can have up to 102 key parts. A composite index can include any of the following index key parts in its index key:
  • One or more columns
  • One or more values that a user-defined function returns (referred to as a functional index).
The index key parts of a composite index can be a combination of columns and user-defined functions.

For dbspaces of the default page size of 2 kilobytes, the total width of all indexed columns in a single CREATE INDEX statement cannot exceed 387 bytes, except for functional indexes of HCL OneDB™, whose language-dependent limits are described earlier in this section. For the maximum sizes in dbspaces larger than 2 kilobytes, see Index-key specification.

Whether the index is based directly on column values in the table, or on functions that take column values as arguments, the maximum size of the index key depends only on page size. The maximum index key size for functional indexes in dbspaces larger than 2 kilobytes are the same as for column indexes. The only difference between limits on column indexes and functional indexes is the number of key parts. An index based on columns can have no more than 16 key parts, but a functional index has different language-dependent limits on key parts. For a given page size, the maximum index key size is the same for both column-based and functional indexes.