Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Given a table with columns A, B, and C of type CHAR(15) and an unique index
If I have a query with a WHERE clause specifying A and C, oracle's optimizer chooses to do an index range scan. But if I know that the cardinality of B is low, should I hint oracle to use an index skip scan? Will a skip scan be more performant skipping a non-leading column (in this case, B) than a range scan? Will the skip scan skip both A and B in the index or will it just skip B?
In general, the optimizer is much better at figuring out whether a range scan would be more efficient than a skip scan (or vice versa). If the optimizer is making the wrong choice, the problem almost always comes down to the human not providing the optimizer with the proper information in the form of accurate statistics on the table and on the indexes (including things like histograms on columns that contain skewed data).
For any given query, the only way to know with certainty which of two plans is more efficient is to try it out with your data on your hardware with your query. You can try adding the hint to force the plan to change and you can then measure which approach is actually more efficient. If the plan the optimizer is choosing isn't the most efficient, I would tend to suspect that the root cause is that some aspect of the statistics are not correct but that is something that you'd need to look at a 10053 trace to get to the bottom of in most cases. Rarely, there are cases where the optimizer is wrong because the optimizer isn't able to take into account some aspect of the data (for example, until recently, it was unable to deal with situations where the data in multiple columns was not independent such as a car table with make, model, and year columns where knowing one value constrained the set of valid values in the other columns).
