Re: Suggestion for application caching

This is mainly a OLTP system. And the data which it stores in application cache is getting frequently queried by users. So are you suggesting is to store these one to many relationship data as denormalized form by flattening it such the the sorting and joining stuff can be avoided ? Not sure how can we avoid "union all", as because these results are from different tables.

But yes, as I modified the query using exists clause, it's using zero temp space.

On Sun, 20 Aug, 2023, 1:09 am Mladen Gogala, <gogala.mladen_at_gmail.com> wrote:

> On 8/18/23 17:21, yudhi s wrote:
>
>
> This query has five "UNION ALL" conditions and the TEMP SPACE is spilling
> while applying the DISTINCT clause mostly. But the application team is
> resisting that they can't get rid of the "DISTINCT" clause because of
> current design and also the UNION ALL is from multiple tables with
> different Join criteria. Also only the specific columns are exposed out of
> the query but not all. Is there any other way to minimize the tempspill
> here?
>
> That means that your model could use some improvement. Using DISTINCT is
> usually a consequence of missing objects. Furthermore, it would probably be
> possible to de-normalize the structures and avoid UNION ALL stuff. Sending
> 20GB to your app servers will probably kill your TEMP space and your
> network. In other words, what you need is not caching, what you need is a
> dose of Ralph Kimball. Look for "Data Warehouse Toolkit" by Ralph Kimball
> on Amazon.
>
> Regards
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

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