Re: Improving query performance further

I did point out that the strategy would be fairly fragile, but there are a couple of points in its favour if the optimizer decides to use adaptive cursor sharing.

1. This is 19c so the histogram could have 2,048 buckets (though the default of 254 might be sufficient anyway)
2. When the low_value and high_value both look like numbers and the input is numeric the optimizer behaves as it's dealing with numbers and that will help with the arithmetic. I've got a note about that on my blog, I think, but can't find it at present.
3. Histograms model skew - and in this case the skew is in the size of range for a given number of rows; histograms don't have to be about capturing "rows per value" (see: https://jonathanlewis.wordpress.com/2016/09/26/pk-histogram/ ). In this case I think the OP has indicated that the data is more dense round the extreme values and sparse in the middle.

Regards
Jonathan Lewis

On Fri, 14 Oct 2022 at 22:33, Lok P <loknath.73_at_gmail.com> wrote:

> Also Jonathan, as the OP mentioned there exists ~120K distinct values
> in both the min and max columns and that to those are varchar2 type
> holding numbers. So the histograms on each of those with max ~254 buckets
> may not be able to give 100% correct estimation for these ranges prdicates
> for all the binds and thus endup impacting the query response.
>
>
>

--
http://www.freelists.org/webpage/oracle-l