Range scan vs Unique Scan vs Skip Scan [closed]

Question

What does unique scan, range scan and skip scan mean? Can we explicitly decide which scan to use? What are the pros and cons of all these scans?

Answer 1

These are pretty self-explanatory by their name:

• A "unique" scan scans for a single value in a unique index.

• A "range" scan starts at some starting value, and reads index entries sequentially (i,.e. along the b-tree) until it encounters a value that runs past a second value (a search for a single value on a non-unique index is a range scan, BTW).

• A "skip" scan uses only the leading column(s) of a composite index to work out its distinct values (so, once it finds a value, it "skips" along that index until it finds the next one).

Each is appropriate (and optimal) for a given type of record matching. The SQL optimizer almost always picks the most appropriate for a given situation (if statistics are up-to-date).