Re: Suggestion for application caching

Thank You Mladen.

The sum of the size's of these three tables are around 250MB and I believe oracle 19C gives free in memory around 10GB. So , do you mean to say putting these three tables in memory in Oracle will help us here? Wondering how that will help in reducing the sorting thing.

Also I am trying to understand how the materialized view will help us here, as because the key issue is Sorting here for those records results out of the Join, even creating a materialized view of the Join result set , won't the refresh will have to sort the whole result set each time it's required to be refreshed?

On Sun, Aug 20, 2023 at 4:31 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 8/19/23 16:34, yudhi s wrote:
>
> 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.
>
> OLTP application returning 20GB to application servers? Good luck with
> that. In the OLTP world there should be no full table scans. What you
> described is better suited for a DW type application. As for caching, I
> would use MariaDB with ENGINE=MEMORY option for tables. Or create a
> materialized view and cache it to memory using In-Memory Oracle option.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

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