Normally for Oracle to use an index, the leading column in the index must be in the predicate. There are conditions, where the leading edge of the index is not in the predicate but the remaining columns in the index can satisfy the predicate. If this condition is true then Oracle can skip over the leading column in the index and use other columns in the index. Note: If the leading column has a low cardinality (few distinct rows) then it may be cheaper for Oracle to skip the leading edge of the index and read starting at the second value in the index.
Lets start by cleaning up the indexes we have already created and build a composite index where the leading column has a low cardinality.
So we now have a composite index on t1 and the leading edge has a very low cardinality; there is only one distinct value in the status column.
So what happened? The Oracle skipped over the status column in the index and used the owner column. But what happens if you have a high cardinality (many district values) for the leading column in the index. Note the cost of this query.
The index skip scan is still being used but now, note the cost of this query. In the first example with a low cardinality column leading the index we had 8 consistent gets. Then when we used a high cardinality column in the leading column we went up to 274 conditions gets. In all it is much more expensive to do the skip scan if the leading column has a high cardinality.