Re: Suggestion for application caching

Response in-line:

On 8/20/23 11:33, yudhi s 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.

Actually, Oracle in-memory can be used until the cached are of 16GB is reached. That doesn't correspond to the table size. Oracle In-Memory option stores data in the columnar format, which includes compression. That is very similar to SAP Hana which is similarly called "in-memory database". 250M shouldn't be a problem. What you need to do is to ensure fast access to the data, not necessarily to cache data in memory. That is why I suggested MySQL or MariaDB. MySQL. Also, the use of DISTINCT means that you're missing an object.

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

If you can create a materialized view which is fast-refreshable, you wouldn't need to sort anything. That is the point.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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