Re: Can we keep a table in memeory for faster access? Oracle 10g

On Mar 3, 3:28 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 03.03.2008 20:15, DA Morgan wrote:
>
> > Sam wrote:
> >> Hi There,
> >> I have a table with heavy select queries on it, with almost no Insert
> >> or Update.
> >> Is there a way to make Oracle keep this table or a View of this table
> >> in memory(RAM)? for faster access?
> >> I have enough RAM on the system and tables are not that big,
> >> Any suggestions?
>
> >> I use:
> >> Oracle: 10g  10.2.1.0
> >> OS: Windows Server 2003
> >http://www.psoug.org/reference/tables.html
>
> > Look up entries related to CACHE and POOL.
>
> Well, Oracle will cache tables anyway so it might not even be needed to
> pin a particular table in memory.  Sam, why do you think you need to do
> this?
>
> Kind regards
>
>         robert

I agree with Robert in that before making use of the cache hint within a query or the Keep Pool for storing the table blocks in the buffer cache (See Daniel's referenced material) you want to be sure you really need to do this.

If the table is small and the blocks are heavily referenced then the table blocks will have a tendency to hang around in the buffer cache as modern Oracle uses a touch count on the blocks to determine the blocks position in the LRU chain.

HTH -- Mark D Powell -- Received on Tue Mar 04 2008 - 09:12:03 CST