Currently we are using a custom/generic strategy to handle the data skew
issue. However, it doesn't work well all the time. For example: SELECT *
FROM t WHERE a between $1 and $2. We assume the selectivity is 0.0025,
But users may provide a large range every time. Per our current strategy,
a generic plan will be chosen, Index scan on A will be chosen. oops..
I think Oracle's Adaptive Cursor sharing should work. First It calculate
the selectivity with the real bind values and generate/reuse different plan
based on the similarity of selectivity. The challenges I can think of now are:
a). How to define the similarity. b). How to adjust the similarity during the
real run. for example, we say [1% ~ 10%] is similar. but we find selectivity 20%
used the same plan as 10%. what should be done here.
I am searching for the best place to invest in the optimizer aspect. and
the above idea should be the one I can think of now. Any thought?
Thanks
--