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 -- Best Regards Andy Fan (https://www.aliyun.com/)
