Re: Performance gain by avoiding lookup table join?

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:

|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. Received on Fri Mar 14 2003 - 16:24:35 CET