Re: Improving query performance further

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.

On Thu, Oct 13, 2022 at 4:26 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Thank you for putting so much effort into describing the data and the
> tests you tried
>
> I think this is a case where there's no "simple" solution that would make
> a significant difference and the type of re-engineering methods that Sayan
> has suggested is likely to be the best bet.
>
>
> Since it's such a small table putting the table and index into a KEEP pool
> may help. Also, since the table is small, creating BOTH indexes (part_col,
> min, max) and (part_col, max, min) might help if we could persuade Oracle
> to pick the right one every time. If you created a histogram on the two
> columns and managed to get Oracle into being clever with adaptive
> cursor-sharing that might be of some assistance - but even if that worked
> most of the time it would be a fairly fragile method.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Wed, 12 Oct 2022 at 20:35, yudhi s <learnerdatabase99_at_gmail.com> wrote:
>
>> Thank you so much for the details.
>>
>> Regarding the table design, the partition column PART_COL is holding
>> three distinct values with equal number of rows in each partition. Also
>> this table is a truncate + load kind of master table in which data is
>> loaded in one of the partitions by truncating that partition daily. And
>> almost all the rows in each of the partitions are mostly the same with very
>> minimal difference. So basically the partition key i.e part_col is not a
>> business column but it's helping the application to cater the 24 by 7
>> availability of master data without being impacted by the truncate+load
>> process. I understand it could have been done with just two partitions but
>> not sure why we are maintaining three partitions here. No update/delete is
>> performed in this table. The column part_col is helping to maintain the
>> primary key along with max_val, low_val, so we can say basically the
>> combination of max_val and min_valis unique only for a specific partition.
>>
>> The oracle version is 19.15.
>>
>

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