The mechanism of Index Skip Scan operation.

What’s the mechanism of index skip scan operation?

Index skip scan was introduced at oracle 9i. As always, new features have pros and cons. So, we must perfectly understand that mechanism.

Why do we need index skip scan operation?

Basically, until 8i, composite index can only be used if the first column exists in the WHERE clause. If index IDX1 is composed of COL1 and COL2, and in the WHERE clause only COL2 exists, then oracle can’t use IDX1 index. COL1 dosen’t exist in the WHERE clause. In that case, if you want to use the index without modifing the query, then you must create an index that has leading edge columns is COL2. But that is a very inefficient work.

How index skip scan operation work?

So, in oracle 9i, oracle coperation introduces index skip scan. That operation can use index even though, the first column dosen’t exist in the WHERE clause. So, oralce query optimizer could be uses the index. And you can also use INDEX_SS hint to control the execution plan. That seems to be very efficient. Really?