Re: Index compression vs. table compression

In article <cstu76$rra$1_at_news-02.connect.com.au>, Howard J. Rogers says...
>
>Thomas Kyte wrote:
>[snip]
>
>>
>> I think most people think of a single key lookup as "a lookup by key that
>> returns a single row -- a lookup".
>> Lookups are generally considered (in my experience) to return a single row.
>
>Not in mine.

lets take a vote --

when you people out there hear "lookup table" -- what do you think of immediately. Is it things like:

turn this zip code into a state name (lookup STATE from POSTAL code) turn this ISBN into a book name and author (lookup name/author from ISBN code) turn this word into a defintion (lookup in a dictionary) turn this ip address into a hostname or hostname into an ip address (dns lookups)

I have personally never heard of someone refering to a lookup table accessed by a key as "something that returns lots of rows"

I have repeatedly heard of a small lookup table accessed by key to be "turn this CODE into a STRING with more meaning for a human being" and such.

>
>Your example, it seems to me, simply demonstrates the smartness of the
>CBO in dealing with what it can determine to be searches for unique or
>nearly-unique data. I have no quarrel with that.
>
>But you are having to carefully arrange for that uniqueness to happen
>(with your update of empno to rownum), and I would suggest that is not
>likely to be generally typical.

Not in most peoples definition of a "LOOKUP" table -- apparently not yours, but if you were to ask around, I think you would find most people think of a lookup as just that.

Sorry, we'll have to agree to vehemently disagree on this -- the "definition" of a lookup that is...

people lookup things in hash tables
people lookup a code to find a text description we lookup ora-xxxxx error messages to find the text. ...

small lookup tables should most definitely be indexed, even a one block lookup table.

no reply really necessary (as we are going to disagree and both sides have been made here) -- but to me "lookup" means "turn this code into some value". I think that is a fairly reasonable and general interpretation of "lookup".

And lookup tables regardless of size want that index.

>>
>> that I said "er, well, actually no -- they are not"
>>
>>
>> a keyed lookup -- single value, single row -- full scan will in fact not win
>> "probably" or "always likely"
>>
>> (opposite is true...)
>
>Then all we can say at the end of this is that your definition of a
>single key lookup is not mine.
>
>I cannot know how many rows will be returned by a query until the lookup
>has taken place. I do, however, know how many key values I am looking up.
>
>Regards
>HJR
>

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