Re: Index compression vs. table compression

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. Received on Fri Dec 31 2004 - 02:14:28 CST