Range scan vs Unique Scan vs Skip Scan
oracle, sql
Solution
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).
Problem
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?