A decent cost based optimizer will be right most of the time, and you have hints for the cases where it falters.
Your approach is simply hiding the query optimizer choices inside of the app, making it more fragile.
Let's say I have two fields: a and b, both of which have an index. And then I have a query with predicates across both a and b. For example, a = 10 and b = 20.
One of the core facilities within an optimizer is selectivity estimation. By looking at the statistics, the optimizer will see that a = 10 might look at 10,000 rows while b = 20 might only look at 10. So the optimal and desired choice would be to use index over b.
However, the exact same query construct with different parameters (e.g. a = 50 and b = 3) might flip the index selection.
Now let's imagine I had to implement this inside of my app. Every time I have such a constraint, the app has to become aware of selectivity to know which indexes to use based on input parameters.
Your approach is simply hiding the query optimizer choices inside of the app, making it more fragile.
Let's say I have two fields: a and b, both of which have an index. And then I have a query with predicates across both a and b. For example, a = 10 and b = 20.
One of the core facilities within an optimizer is selectivity estimation. By looking at the statistics, the optimizer will see that a = 10 might look at 10,000 rows while b = 20 might only look at 10. So the optimal and desired choice would be to use index over b.
However, the exact same query construct with different parameters (e.g. a = 50 and b = 3) might flip the index selection.
Now let's imagine I had to implement this inside of my app. Every time I have such a constraint, the app has to become aware of selectivity to know which indexes to use based on input parameters.