If you often use Where clauses that involve more than one field, you may want to build an index containing multiple fields. Consider the following Where clause:
WHERE last_name = 'Smith' AND first_name = 'Thomas'
For this condition, the optimal index field expression is last_name, first_name. This creates a concatenated index.
Concatenated indexes can also be used for Where clauses that contain only the first of two concatenated fields. The last_name, first_name index also improves the performance of the following Where clause (even though no first name value is specified):
last_name = 'Smith'
Consider the following Where clause:
WHERE last_name = 'Smith' AND middle_name = 'Edward' AND first_name = 'Thomas'
If your index fields include all the conditions of the Where clause in that order, the driver can use the entire index. If, however, your index is on two nonconsecutive fields, for example, last_name and first_name, the driver can use only the last_name field of the index.
The driver uses only one index when processing Where clauses. If you have complex Where clauses that involve a number of conditions for different fields and have indexes on more than one field, the driver chooses an index to use. The driver attempts to use indexes on conditions that use the equal sign as the relational operator rather than conditions using other operators (such as greater than). Assume you have an index on the emp_id field as well as the last_name field and the following Where clause:
WHERE emp_id >= 'E10001' AND last_name = 'Smith'
In this case, the driver selects the index on the last_name field.
If no conditions have the equal sign, the driver first attempts to use an index on a condition that has a lower and upper bound, and then attempts to use an index on a condition that has a lower or upper bound. The driver always attempts to use the most restrictive index that satisfies the Where clause.
In most cases, the driver does not use an index if the Where clause contains an OR comparison operator. For example, the driver does not use an index for the following Where clause:
