Life of an Oracle DBA – The Basics Access Paths: Index Skip Scan

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.

image

Lets start by cleaning up the indexes we have already created and build a composite index where the leading column has a low cardinality.

image

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.

image

image

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.

image

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.

This entry was posted in Uncategorized by rlockard. Bookmark the permalink.

About rlockard

Robert Lockard is a professional Oracle Designer, Developer and DBA working in the world of financial intelligence. In 1987 his boss called him into his office and told him that he is now their Oracle Wizard then handed him a stack of Oracle tapes and told him to load it on the VAX. Sense then, Robert has worked exclusively as an Oracle database designer, developer and Database Administrator. Robert enjoys flying vintage aircraft, racing sailboats, photography, and technical diving. Robert owns and fly’s the “Spirit of Baltimore Hon” a restored 1948 Ryan Navion and lives in Glen Burnie Maryland on Marley Creek

Welcome to oraclewizard