Re: Performance gain by avoiding lookup table join?

andrewst_at_onetel.net.uk (Tony) wrote in message news:<c0e3f26e.0303140724.41d22c88_at_posting.google.com>...
> joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0303101741.7f7f732d_at_posting.google.com>...
> > The consultant is basically suggesting a denormalization trick, and
> > could be correct or could be useless (as the other posts suggest, an
> > empirical test is the only way to tell). However, the answer is "it
> > depends," because empirical testing is very specific and may not
> > generalize to all possible future situations, which may flip it over
> > to "way off." Denormalization quite possibly may work against certain
> > combinations of queries that don't need the join, by making full table
> > scans take longer because the table is bigger. Also, CBO may decide
> > things quite differently in the different physical layouts - and a
> > hint may change things radically in many situations. A five row table
> > will probably stay in memory if it is being used - the denormalized
> > table might be able to keep fewer rows in memory for certain kinds of
> > updates. In some borderline situations, adding a little bit to a
> > table can make fewer rows fit in a block, even causing chaining where
> > there wasn't before.
> >
> > Hopefully your consultant can explain how he accounted for all this to
> > you.
> >
> > jg
>
> No, this isn't a denormalisation, it is using a natural key instead of
> a surrogate. The revised tables would be:

I think you are right, although if there truly winds up being just the one column, it would certainly be better to just use constraints and eliminate the table entirely - perhaps the design comes from olden times Before Constraints When Disks Were Expensive. If the data is so dense that it would make a big difference in performance (due to filled blocks or whatever), it probably needs some classical DBA analysis anyways.

>
> --------------
> |PERSONS | 3 million rows
> | pers_id (PK) |
> | Forename_1 |
> | Surname |
> | etc |
> --------------
> |
> |
> /|\ 5 million
> -------------- rows ----------------
> |PERS_ADDR_ROLE| |ADDR_ROLE_TYPES | approx 5 rows
> | pers_id | | addr_code | e.g. 'HOME'
> | addr_id |\ | | 'WORK'
> | addr_code |->-------| | 'POSTAL'
> | start_date |/ | |
> | end_date | | |
> -------------- ----------------
> \|/
> |
> |
> --------------
> |ADDRESSES | 4 million rows
> | addr_id |
> | Addr_Line_1 |
> | Addr_Line_2 |
> | etc |
> --------------
>
> Unless the ADDR_ROLE_TYPES table has any other columns not shown here,
> it could now be dropped and the foreign key om PERS_ADDR_ROLE replaced
> by a chack constraint.
>
> The consultant may well be right - but the only way to know is try it.
> In any case, the design would be cleaner and clearer that way.

jg

--
_at_home.com is bogus.
Remember rivers, Nellie?