Try not to use two indexes when one would do. If searching for SURNAME and FIRSTNAME, do not unnecessarily create separate indexes for each column. Instead, create a concatenated index on both SURNAME and FIRSTNAME. You can use the leading portion of a concatenated index on its own. If you sometimes query on the SURNAME column without supplying the FIRSTNAME, then SURNAME should come first in the index.
Over-index to avoid a table lookup
Sometimes you can improve SQL execution by over-indexing. Over-indexing involves concatenating columns that appear in the SELECT clause, but not in the WHERE clause to the index.
For example, if you are searching on SURNAME and FIRSTNAME in order to find EMPLOYEE_ID, the concatenated index on SURNAME and FIRSTNAME allows you to quickly locate the row containing the appropriate EMPLOYEE_ID. However, you need to access both the index and the table. If there is an index on SURNAME, FIRSTNAME, and EMPLOYEE_ID, the query can be satisfied using the index alone. This technique can be particularly useful when optimizing joins, since intermediate tables in a join are sometimes queried merely to obtain the join key for the next table.
Consider advanced indexing options
Oracle default B*-tree indexes are flexible and efficient. They are suitable for the majority of situations. However, Oracle offers a number of alternate indexing schemes that can improve performance in specific situations. These include:
Make sure your query uses the best index
Novice SQL programmers are often satisfied if the execution plan for their SQL statement uses any index. However, there is sometimes a choice of indexed retrievals, and the Oracle optimizer (especially the older rule-based optimizer) does not always choose the best index. Make sure that the indexes being selected by Oracle are the most appropriate, and use hints to change the index if necessary.