Range Scan vs. Unique Scan vs Skip Scan

Question

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

Answer 1

They are pretty clear by name:

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

• The “range” scan starts from a certain initial value and sequentially reads the index entries (i, .e. Along the b tree) until it encounters a value that runs through the second value (the only value for a unique index is a range scan, BTW).

• The skip scan uses only the leading column of the composite index to develop its various values ​​(therefore, as soon as it finds a value, it skips over that index until it finds the next one).

Each of them is suitable (and optimal) for a certain type of correspondence of records. The SQL optimizer almost always chooses the most suitable for a given situation (if the statistics are relevant).