Since indexes speed up searches, it is often helpful for a table to have one index for each attribute (or combination of attributes) that is used frequently in searches. All indexes other than the primary index are called secondary indexes, and can be created by using the CREATE INDEX statement, see CREATE INDEX.
A table can have as many indexes as required, as long as each index has a unique combination of columns, order of columns, and order of values. For example, in the following code, the third index duplicates the first index and either generates an error message or wastes disk space with duplicate information.
CREATE INDEX i1 ON TABLE t1 (col1, col2); -- The following statement is ok because although the columns are -- the same as in index i1, the order of the columns is different. CREATE INDEX i2 ON TABLE t1 (col2, col1); -- The following statement is not ok because index i3 would be -- exactly the same as index i1. CREATE INDEX i3 ON TABLE t1 (col1, col2); -- ERROR. -- The following statement is ok because although the columns and -- column order are the same, the order of the index values -- is different (by default, indexes are ascending) CREATE INDEX i3b ON TABLE t1 (col1, col2) DESC;
If one index is a leading subset of another index (the columns, column order, and value order of all N columns in one index are exactly the same as the first N column(s) of another index), then you only need to create the index that is the superset. For example, suppose that you have an index on the combination of DEPARTMENT + OFFICE + EMP_NAME. This index can be used not only for searches by department, office and employee name together, but also for searches of just the department, or just the department and office together. So there is no need to create a separate index on the department name alone, or on the department and office alone. The same is true for ORDER BY operations; if the ORDER BY criterion matches a subset of an existing index, then the server can use that index.
Keep in mind that, if you defined a primary key or unique constraint, that key or constraint is implemented as an index. Thus you never need to create an index that is a leading subset of the primary key or of an existing unique constraint; such an index would be redundant.
When using a secondary index for read operations, if the server finds all the requested data in the index key, the server does not need to look up the complete row in the table. However, if the user updates values in the table, then the data rows in the table as well as the values in any indexes must be updated.