Re: Index compression vs. table compression

Richard Foote apparently said,on my timestamp of 23/01/2005 12:53 PM:

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

Without weighing in on one side or the other of the original discussion, just a small comment on all this FTS vs Index with lookup tables:

Not just UK or PK references, I'm afraid. Data entry and validation is usually different. If I may use the ubiquitous POSTCODE table (don't get me started on our Ozzie postcodes...) I've seen many, many queries on it by range of area code name, as in 'WAR%' for example to get WARRIEWOOD as well as WARBURTON.

You know what I mean: user types a single letter followed by % to get all the postcodes starting from that letter, then picks the one they want and the real PK (the postcode number itself) gets copied somewhere else. I have Forms applications in mind here.

Just one minor but very common instance of a non-PK/UK access. It doesn't change the POSTCODE table being a lookup table in the "Oracle-ish" meaning. Or your point about accesses via PK always using the index.

The same reasoning applies to the DEPT table: someone might do a search by DEPTNAME before dropping DEPTNO somewhere else.

Of course if someone designs POSTCODE using the area code name as the PK, then all bets are off: if the range is wide enough, the CBO will most likely FTS. We talked about this a while ago, IIRC, and you made your point quite clearly back then how low the percentage of rows in a range really is nowadays to trigger a FTS.

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
wizofoz2k_at_yahoo.com.au.nospam