Re: Index compression vs. table compression

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 31 Dec 2004 00:45:23 -0800
Message-ID: <41d51085$1_3@127.0.0.1>

Jonathan Lewis wrote:

> Notes inline
>
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Public Appearances - schedule updated Dec 23rd 2004
>
>
>
>
>
>
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:41d49207$0$3805$afc38c87_at_news.optusnet.com.au...
>
>

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

>
>
> Not if they're being used for doing lookups, I hope.
>
>

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

>
>
> But a 'genuinely small' table - which means less than 20
> blocks or 2% of the number of buffers in the cache,
> whichever is larger - is not automatically loaded to the
> discard end of the LRU list, anyway
>
>

>>                                   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*.

>
>
> Since we are (should be) on at least 8i at present (and only for another
> 16 hours in my timezone) nothing is ever read into the hot half of the
> LRU list. At best it could be described as the top of the cold half.
> Blocks are only promoted to the hot half after they have traversed
> the cold half and are found to be suitable candidates for promotion
> as they reach the discard end of the cold half.

If you'll write another book, with material such as the above, I promise to purchase a minimum of 50 copies in the first year.

Thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Dec 31 2004 - 02:45:23 CST