Re: Suggestion for application caching

Hi,

As far as I know, in-memory VECTOR GROUP BY does not support distinct operations and it takes issue with Bloom filters as well. Since you're on RAC, in-memory has extra challenges there if you do not use PQ or can't duplicate segments on all nodes for some reason.

Out of curiosity, what does "very fast response" mean in terms of milliseconds and operations per second?

On Sun, 20 Aug 2023 at 17:35, yudhi s <learnerdatabase99_at_gmail.com> wrote:

> 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