To create a composite index on the combined values in all the specified columns, specify two or more column names.
Use composite indexes when two or more columns are best searched as a unit, for example, the first and last names of authors in the authors table. List the columns to be included in the composite index in sort-priority order, inside the parentheses after the table name, like this:
create index auth_name_ind on authors(au_fname, au_lname)
The columns in a composite index do not have to be in the same order as the columns in the create table statement; for example, you can reverse the order of au_lname and au_fname.
You can specify up to 31 columns in a single composite index. All the columns in a composite index must be in the same table. See Chapter 4, “Table and Index Size,” in the Performance and Tuning Series: Physical Database Tuning.
