Re: Improving query performance further

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