Restrictions on the Number of Indexes on a Set of Columns

You can create multiple indexes on a set of columns, provided that each index has a unique combination of ascending and descending columns. For example, to create all possible indexes on the stock_num and manu_code columns of the stock table, you could create four indexes:

• The ix1 index on both columns in ascending order
• The ix2 index on both columns in descending order
• The ix3 index on stock_num in ascending order and on manu_code in descending order
• The ix4 index on stock_num in descending order and on manu_code in ascending order

Because of the bidirectional-traversal capability of the database server, you do not need to create these four indexes. You only need to create two indexes:

• The ix1 and ix2 indexes achieve the same results for sorts in which the user specifies the same sort direction (ascending or descending) for both columns, so you only need one index of this pair.
• The ix3 and ix4 indexes achieve the same results for sorts in which the user specifies different sort directions for the two columns (ascending on the first column and descending on the second column or vice versa). Thus, you only need to create one index of this pair. (See also Bidirectional Traversal of Indexes.)