A simple index lists only one column (or for IDS, only one column or function) 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.
In Dynamic Server, if you use SET COLLATION to specify a non-default 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.
In Dynamic Server, 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 have any of the following items as an index key:
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 Dynamic Server, whose language-dependent limits are described earlier in this section.
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 non-functional indexes. The only difference between limits on column indexes and functional indexes is the number of key parts. A column based index can have no more than 16 key parts and 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.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]