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: Index compression vs. table compression

Re: Index compression vs. table compression

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 31 Dec 2004 10:40:48 +1100
Message-ID: <41d49207$0$3805$afc38c87@news.optusnet.com.au>


Rick Denoire wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote:
>
>

>>No they don't. They say that it is unnecessary and less effective than 
>>the multiple buffer pools feature you seem not quite to grasp properly. 
>>CACHE was an attempt to keep large full table scans at the cold end of 
>>the LRU list, and thus prevent the warm half from being flushed out. 
>>Precisely what the RECYCLE pool does. Only the RECYCLE pool does the job 
>>more effectively and more certainly, because it's not trying to 
>>'partition' a single LRU list, but has one all to itself.

>
>
> To my understanding, the CACHE option does the opposite: put the data
> in the MRU, e.g, at the hot end, of the cache. In this case, it can't
> be ever meant to be something similar to the use of the RECYCLE pool!
>
> Would anybody correct me if I am wrong?
>

ALL tables, other things being equal, get put at the hot end of the LRU list (and I wish you'd get your terminology correct: it's a LEAST recently used list, not an MRU one). But that would clearly be inappropriate for tables read by FTS, because those would end up dislodging useful data out of the cache. Therefore, we want a mechanism that will say "if you are read by a FTS, stay at the cold end of the LRU list, even though you are actually the most recently used block"... and that is precisely what the *NOCACHE* clause does. But there is then a further problem: how is the optimiser likely to read small, useful, lookup tables?.. er, via a FTS, probably, if they are genuinely small. And therefore a further tool is needed: a mechanism which will distinguish between nasty, huge FTSes of bulky tables, and small, OK, FTSes of useful lookup tables. And that is what the *CACHE* clause does: if you specify it as an attribute of a small lookup table, its blocks will indeed be read into the hot half of the LRU list, *even though they were read by a FTS*.

Putting it simply: the CACHE/NOCACHE clause tries to draw a distinction between small tables that can be read with a FTS without harm, and big tables that can only be read by a FTS by causing considerable disruption to the buffer cache contents. And if I confused you by only mentioning one of the nevertheless inextricably-related pair of keywords earlier, that was an oversight on my part.

And that is precisely what the KEEP versus the RECYCLE pools set out to do, too.

Only, instead of trying to draw a vague distinction between halves of a single LRU list, the separate buffer pools create entirely separate buffer cache areas so that no matter how cold a small table might happen to get, it still can't be dislodged from its keep cache by an inadvertent large FTS directed to a RECYCLE cache... something that the CACHE/NOCACHE mechanism simply cannot ever guarantee.

All of which boils down to: CACHE/NOCACHE and KEEP/RECYCLE are trying to do precisely the same thing.

HJR Received on Thu Dec 30 2004 - 17:40:48 CST

Original text of this message

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