Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Caching within packages..

Re: Caching within packages..

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 10 Dec 2003 21:08:15 GMT
Message-ID: <3XLBb.34$i66.14@twister.socal.rr.com>


Joachim Zobel wrote:

> On Tue, 09 Dec 2003 22:43:37 +0000, NetComrade wrote:
>
>

>>Either one of o'reilly books, or some other book, talked about how if
>>you declare a function/procedure as part of a function, you can cache
>>objects in an array for the life of a session, so u don't have to make
>>a trip to a table again.. anyone remembers which one?

>
>
> If you are using PL/SQL from inside the database its not a trip. And the
> database already does caching. You can tune it with hints, create table
> options and by using a keep buffer cache.
>
> Joachim

But you can't eliminate the context switch and that's probably what the OP really meant by "trip". I have seen some circumstances where caching a lookup table in memory this way can produce a dramatic performance improvement. There are times when the answer is very time consuming to produce as well. Why bother inserting those results in a global temporary table and querying from there when the in memory solution is so easy? With the introduction of associative tables in 9.2 it's been made even easier.

Of course the other posters concerns about what happens if the table being cached is modified has to be addressed. In reality, there are many times when you know the data cannot be changing or you just don't care as long as your cache is consistent with one point in time.

--
Richard Kuhler
Received on Wed Dec 10 2003 - 15:08:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US