RE: How to fix cache buffer chain issue
Date: Tue, 22 Jun 2021 17:31:15 -0400
Message-ID: <693601d767ad$eea590b0$cbf0b210$_at_rsiz.com>
JL previously suggested:
“If you rewrite the first bit of this to:
ADT >= trunc(:b2) - 1,
then add ADT to the index on dt_cr, this should allow Oracle to resolve the predicate in the index and may therefore reduce the visits to the table.”
IF you do that (and the corresponding “between” resolutions) AND add ALL to your unions, you should resolve which rows you need from the index without a table visit and therefore experience goodness.
NOT having to perform unneeded de-duplication in theory should prevent unneeded insertion into a hash (or sorting) along the way to get the final result set.
Now it appeared there were some missing ranges of ADT in the filter sets (days 15 through 59, unless JL just got tired of the tedious typing), so whether it is cheaper to read the whole day from the table filtering or accessing just the rows you need filtered by an improved index requires actuals testing.
My perception was that it was going to be a chore for y’all to convert to a case statement and test that. I could be wrong about that. I have no tangible support for my notion that adding ADT to your index on DT_CR will prove of benefit in the rest of your code base for relatively little cost.
Good luck.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap
Sent: Tuesday, June 22, 2021 2:23 PM
Thank You Mark.
Agree that the date ranges(filter on ADT) seem to be non overlapping and in that case we can replace the UNION with UNION ALL. But even in that case won't the table/index access for table TAD, (which is mostly the concern here) is going to happen the same 17 times i.e. as many times the number of UNION clauses are there in the query? I think what Jonathan is suggesting is to get rid of all the 17 UNION clauses and make the same logic achieve in one query, I am still trying to understand that one.
Regards
Pap
On Mon, Jun 21, 2021 at 8:00 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
AND (not but), this analysis (non-overlapping and therefore non-duplicative ranges) indicates UNION ALL can in fact be used in place of UNION. JL’s “get it all” and produce the pieces by case probably does even better than that, but considering code change cost versus making problems go away, changing UNION to UNION ALL might be good enough.
(Quite apart from that I wonder whether the rest of the query code on this data set screams to be partitioned by DT_CT and not use an index instead of a partition scan.)
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Monday, June 21, 2021 4:06 AM
Thanks, Mladen and Andy, for prompting me to look at the original SQL.
Without spending more than a couple of minutes on the code I get the impression that it's trying to
The code seems very repetitive with the main variation being:
AND (TRUNC ( :B2) - ADT) <= 1
A "global" improvement might be to extract all the data for the date and then use something like a case expression to sum the data by the required data ranges.
Regards
Jonathan Lewis
To: Mark W. Farnham
Cc: Jonathan Lewis; Andy Sayer; Dominic Brooks; Mladen Gogala; Oracle L
Subject: Re: How to fix cache buffer chain issue
To: Andy Sayer
Cc: Dominic Brooks; gogala.mladen_at_gmail.com; oracle-l_at_freelists.org
Subject: Re: How to fix cache buffer chain issue
AND (TRUNC ( :B2) - ADT) > 1
AND (TRUNC ( :B2) - ADT) <= 2
AND (TRUNC ( :B2) - ADT) > 2
AND (TRUNC ( :B2) - ADT) <= 3
AND (TRUNC ( :B2) - ADT) > 3
AND (TRUNC ( :B2) - ADT) <= 4
AND (TRUNC ( :B2) - ADT) > 4
AND (TRUNC ( :B2) - ADT) <= 9
AND (TRUNC ( :B2) - ADT) > 9
AND (TRUNC ( :B2) - ADT) <= 14
AND (TRUNC ( :B2) - ADT) > 60
AND (TRUNC ( :B2) - ADT) <= 90
AND (TRUNC ( :B2) - ADT) > 90
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 22 2021 - 23:31:15 CEST