Re: Index compression vs. table compression

Richard Foote wrote:

>>>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.
>
>
> That Howard perhaps says more your experience than you might have
> intended....

Richard, if you wish to get personal about it, be my guest. But I don't need to justify my experience to you (nor indeed to anyone else).

> Lookup tables

Excuse me... but Tom wrote about "most people think a single key lookup is X" and you've just re-written that to read "most people think a lookup table is X". I don't need lessons on what a lookup table is. I think you might need lessons on the difference between a verb and a noun, however.

> by *definition* are (generally) small tables that contain
> dimension data that are used in relational databases to store attributes of
> fact tables. They are the tables that are referenced via Primary (or in some
> instances Unique) keys to determine the required attributes and are
> referenced (hopefully) via FK relationships from the parent tables.
>
> So for example, if I want to know specific attribute details of an employee,
> I would *lookup* the employee details via the PK. I would not have a table
> that has multiple occurrences of the same key as it must therefore be a
> parent table and not a lookup table by definition.
>
> This "definition" btw can be found in a number of the Oracle manuals
> (Performance Tuning, Data Warehouse, etc.).
>
> If you use the term "lookup table" and not mean the above definition or what
> Tom, Jonathan and Daniel have been trying to teach you, then that's really
> "sloppy" writing I'm afraid.

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.

> Are you seriously suggesting that this is the best you can do to get out of
> this one. Let's go back to your original quotes:

I'm not trying to get out of anything. What I wrote stands. You wish to pick it to bits, because it suits your agenda, whatever that happens to be these days. (And if we're going to start quoting, why not quote your 'goodbye group, this is no longer fun' one? What changed, I wonder?)

> "how is the optimiser likely to read small, useful, lookup tables?.. er, via
> a FTS, probably, if they are genuinely small"
>
> and
>
> "A small table is always likely to be read via a FTS using CBO. Even for a
> single key lookup..."
>
> Note the word *always*. '

Note the word "likely".

>Boy are these examples of "sloppy" writing....

Boy, and are these desperately bitchy attempts to prove a point grasped at like the last straw in the shop.

> As everyone else has been trying to teach you, if the cardinality is low
> enough, even for very small tables, an index access path is most efficient.
> In the case of a single key lookup, almost certainly so.
>
> As Jonathan pointed out, this is a myth that you have propagated with you're
> text, whether you get it or not.

I posted an example that contradicts you Richard. So deal with it.

> You have now come back with a table scan that reads over 7% of the data and
> say, hey look, I'm right, it's using the FTS. Note that if the table were to
> be 10 million rows in size, it might also be using the FTS because of the
> relatively high selectivity of the query.

"Might". "Likely". Apparently, my use of language in this thread has been sloppy, but yours isn't? I don't think so. Weasel away, Richard.

> Point is, small table lookups use an index for low cardinality queries, just
> like their bigger cousins, a point you certainly overlooked both times in
> what you said.

Whatever. I stand by every statement I made. The ones under discussion here are not wrong when read as they were written. Your wilful misreading of them is the issue here, I think.

> This is a nonsense. Lookup tables have these things called *Primary Keys*.

And who said we were discussing lookup tables? As Jonathan has pointed out to me in an email, yes I mentioned them when talking to Rick. But the sentence that has been so eagerly leapt on by you was one made in reply to Jonathan's interjection into that conversation. And it referred to a single key lookups, not a lookup table.

> PKs are (or should be) a mandatory characteristic of a lookup table else
> child/fact tables will not be able to reference them via a FK. Lookup tables
> are primarily accessed via their PK and primarily return *one* row as a
> result.
>
> In your "example", there is no PK. You Howard have manufactured a table that
> is by definition, not a lookup table,

It is you that is going doolally about lookup tables, Richard. I never mentioned them at the point where all the excitement started.

I know what I wrote. I know what I meant. The words mean what they say. And you are *adding* to them to try and get them to mean something else.

> In short Howard, your quote is misleading and wrong in suggesting that small
> table lookups are *always* likely to be read via a FTS and your example is
> as far removed from a lookup table as can be.

Thank you. You yourself have now used the phrase "small table lookups". Am I supposed to assume that you mean by that "use of a small lookup table"? Because in my book, they are two different things. And whilst the index issue is true for one, it is not true for the other. As my post to Tom demonstrated.

> Yes you can. In a dimension, lookup

Yawn. Let me repeat: it is you that are interpreting references to "lookup" the verb as "lookup table" the noun.

> table that is standard to every
> relational database worth knowing, you will return 1 (or perhaps 0) rows. No
> more, because if you did, it would not be a lookup table because you'll be
> forced to perform a m-m relationship join.
>
>
>>I do, however, know how many key values I am looking up.
>
>
> Most people when they make a mistake pointed out to them say something akin
> to "Oopps, fair enough, thanks for the correction".
>
> Most people that is ...
>
> Cheers
>
> Richard

If, Richard, you would care to point out a mistake in the words I actually used, and not in the interpretation you wish to foist on them by interpolating words I never used, then feel free to do so.

I have no idea why you are so desperate to persuade yourself and others that I have made a mistake. Is Daniel paying you or something? After all, he recently posted a comment that I never apologise for making an error, just as you have come close to here... to which my response at that time was a quick search on Google and the finding of 98 (if I remember correctly) such occasions.

If you have a personal agenda, Richard... please take it elsewhere.

HJR Received on Sat Jan 22 2005 - 20:18:52 CST