Re: Index compression vs. table compression

In article <csv1if$fnr$1_at_news-02.connect.com.au>, Howard J. Rogers says...
>
>Richard Foote wrote:
>

...

>
>But, Richard, in the sentence which Tom has been elaborating on, and
>which was made in response to Jonathan, I made no mention of lookup tables.
>

we are exceedingly close to beating a rather dead horse, but -- as for putting words into mouths of others -- my posting had verbaitim cut and pastes which I'll tag with initials here:

Author: "Howard J. Rogers" <hjr_at_dizwell.com> Date: Fri, 31 Dec 2004 10:40:48 +1100

HJR>>>is then a further problem: how is the optimiser likely to read small, 
HJR>>>useful, lookup tables?.. er, via a FTS, probably, if they are genuinely 
HJR>>>small.

Author: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> Date: Fri, 31 Dec 2004 08:14:28 +0000 (UTC) JPL>> Not if they're being used for doing lookups, I hope. JPL>> Author: "Howard J. Rogers" <hjr_at_dizwell.com> Date: Sat, 01 Jan 2005 03:11:59 +1100

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

You actually did use "lookup tables". You said lookup tables, Jonathan said lookup table, you changed it to "single key lookup" after the fact, but we were in fact discussion lookup tables -- which will use an index, not a full table scan.

So, when talking about lookup tables, a keyed read of a single row, indexes rule.

When talking about large range scans, the optimizer will choose a full scan or range scan depending on many factors including the estimate number of rows to be retrieved, clustering factor, optimizer_index_* settings, multi-block read counts etc.... It is never an "always" situation, it is always "it depends" situation.

...
>
>HJR

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation