RE: How to fix cache buffer chain issue

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
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    

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

1. get all the data for a given date DT_CT, then
2. summarise it into non-overlapping ranges of adt.

            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

 

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  

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