Re: Index compression vs. table compression

Thomas Kyte wrote:
> 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 --

Well, I hate to sound like a tyrant, but since it was my sentence in the first place, I think I'm best placed to know what I meant by it.

> 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

But I never referred to a lookup table, either. I referred to a 'single key lookup'. As you can no doubt check.

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

Tom: I didn't refer to a lookup table. I referred to a single key lookup. Lookup as a verb, not a noun. And by it, I meant "looking up some data in a table, searching for a single key value". Which is what one does whilst searching through EMP looking for the row with ename='KING', or empno=7934... where uniqueness might or might not be an attribute of the data being searched.

Now, if my use of language is unusual, and it needed clarifying to get the point across, then that's fine: your intervention has been helpful in doing precisely that. Though I have to say that I don't regard the usage I actually used as unusual. But that's your privilege, along with everyone else's of course.

But let's not start putting words (specifically, nouns) into my mouth that were never there, eh?

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

Since a genuine lookup table does indeed turn A code into A longer value, it almost goes without saying that its contents will be unique. And I have no problems whatsoever with agreeing that unique values will probably get an index whether they like it or not; nor that unique values will actually benefit from having such an index, no matter the size involved.

But, and I guess we will just leave it here, that is not what I was originally saying nor referring to.

Incidentally, it's quite interesting to me that you use that specific phrase 'lookup table', since I was using it back in 1987 in my Dataflex days... but I have never seen the term used in the Oracle documentation or training material, where the term 'dimension tables' seem to stand in for much the same thing. Indeed, I have been guilty in the training room of explaining the word 'dimension' as meaning 'lookup' -because that's the term I grew up with, and feel more comfortable with. Which is all the more reason why, if I had meant to use it, I would have.

Regards
HJR Received on Sat Jan 22 2005 - 14:19:18 CST