Re: Index compression vs. table compression

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 01 Jan 2005 03:11:59 +1100
Message-ID: <41d57a50$0$5112$afc38c87@news.optusnet.com.au>

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.

Why?

A small table is always likely to be read via a FTS using CBO. Even for a single key lookup...

>

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

Forget the "genuinely small". Deal with the actual issue being discussed here. How do you distinguish between benign and bad FTSes?

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

Whatever. Does this change the conclusions to be drawn from anything I've written? If not, say so. If yes, explain why.

HJR Received on Fri Dec 31 2004 - 10:11:59 CST