Re: How to fix cache buffer chain issue

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
> *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.
>
>
>
> The code seems very repetitive with the main variation being:
>
> AND (TRUNC ( :B2) - ADT) <= 1
>
>
>
> 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