Re: Improving query performance further

That's not varchar2. Histograms show that's a number column

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE
http://orasql.org

On Sat, 15 Oct 2022, 00:34 Mark W. Farnham, <mwf_at_rsiz.com> wrote:

> 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