RE: Improving query performance further

I wonder if a test of a function based index (to_number of each column) would perform better, just because it would likely be a lot smaller.  

I still think he should get rid of partitioning rotating by rotating a synonym.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lok P Sent: Friday, October 14, 2022 5:34 PM
To: jlewisoracle_at_gmail.com
Cc: Oracle L
Subject: 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