When INDEX SKIP SCAN is used

According to article Concatenated Indexes(http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys), I would guess that optimizer will go via INDEX SKIP SCAN access path when on situation:

CREATE UNIQUE INDEX employee_pk ON employees (employee_id, subsidiary_id);

SELECT first_name, last_name FROM employees WHERE subsidiary_id = 20;

Especially with growing distinct count of values in subsidiary_id.

When will optimizer decide for INDEX SKIP SCAN???

asked Mar 07 '14 at 17:11

mattsk
1●1●1●1

One Answer:

The optimizer will use a skip scan when it's estimates a lower cost for the skip scan as for any alternative access method. Generally, the more distinct value the leading (skipped) column of the index has, the more unlikely the skip scan gets. Further, in this case it must access the table anyway (to fetch the selected columns) so an rather inefficient SKIP SCAN + TABLE ACCESS is not a very tempting option.

answered Mar 09 '14 at 14:30

Markus Winand ♦♦
936●5●11●20