The DB2 (we’re talking DB2 for LUW here) query optimizer usually does a pretty good job at choosing a good execution plan, and normally you would not want to coerce it, with optimization profiles or weird search predicates, to go where it does not want to. However, once in a while one does feel the urge to give it a good kick in the right direction.
Imagine a query like this:
SELECT something FROM a_table WHERE start_time BETWEEN ? AND ?
How is the optimizer supposed to know what would be the selectivity of such a predicate? It can’t, so it will take a guess, which in some cases will be wrong (although you can’t really blame it, can you). Suppose
a_table contains some sort of log records for the past three years,
start_time is a timestamp, and you’re only interested in a range of few hours at a time. How can you explain to the optimizer that you only expect about a hundred of rows as a result, not a hundred million, and it better choose the index on
start_time for access?
Simple, you tell it:
SELECT something FROM a_table WHERE start_time BETWEEN ? AND ? SELECTIVITY 0.00001