Update: I have changed this post based on Graham’s recent comments regarding Trace Magic since the trace settings that I recommended were not actually compatible with Trace Magic. Please see the following link for information regarding Trace Magic:
However, when manually reading a trace file (e.g. in Sublime Text, Notepad++, etc.) I would recommend the following trace settings:
-TRACE 135 -TOOLSTRACESQL 31 -TOOLSTRACEPC 4044
I still find these settings useful for non-performance related debugging issues.
When a single index has multiple columns that are indexed, it is called a composite or concatenated index.
Prior to the introduction of skip-scan functionality, queries could only use the index if the leading column of the index was used in the WHERE clause. Consider the example in the following listing where the EMP table has a concatenated index on Empno, Ename, and Deptno. Note that Empno is the first part, Ename is the second part, and Deptno is the third part. If you are not making use of the skip-scan functionality, Oracle will generally not use this index unless your WHERE clause specifies a value for the leading column (Empno).
select job, empno
where ename = 'JAY';
Since Ename is not the leading column of the index, the optimizer may elect not to use the index. With the introduction of the skip-scan functionality in Oracle 9i, the optimizer may choose to use the index even though an Empno value is not specified in the WHERE clause. Instead, the optimizer could choose to perform a fast full scan of the index or a full scan of the table.
The same holds true if the third column of the index is used in the WHERE clause:
select job, empno
where deptno = 30;
In this listing, the WHERE clause specifies a value for the third column in the index. The optimizer may select to perform an index skip-scan access, an index fast full scan, or a full table scan. By creating the index, you have given the database more choices to consider when executing the query, hopefully improving the overall performance. Note that the user’s code does not change; the optimizer is aware of the index and bases its decisions on the anticipated cost of each alternative.
Niemiec, R. J. (2007). Oracle Database 10g Performance Tuning Tips & Techniques: Oracle Press.