Re: Performance gain by avoiding lookup table join?

From: Joel Garry <joel-garry_at_home.com>
Date: 10 Mar 2003 17:41:17 -0800
Message-ID: <91884734.0303101741.7f7f732d_at_posting.google.com>


charlie3101_at_hotmail.com (Charlie Edwards) wrote in message news:<217ac5a8.0302270403.6a1f4b61_at_posting.google.com>...
> Consider the following set-up (8.1.7):
>
>
> --------------
> |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_type_id | e.g. 1 'HOME'
> | addr_id |\ | addr_code | 2 'WORK'
> | addr_type_id |->-------| | 3 'POSTAL'
> | start_date |/ | |
> | end_date | | |
> -------------- ----------------
> \|/
> |
> |
> --------------
> |ADDRESSES | 4 million rows
> | addr_id |
> | Addr_Line_1 |
> | Addr_Line_2 |
> | etc |
> --------------
>
> It has been suggested by a consultant that it is more efficient to
> avoid joining to the ADDR_ROLE_TYPES table and instead have the
> ADDR_CODE column on the PERS_ADDR_ROLE table, instead of using the
> surrogate key.
>
> Well, I can see logically that avoiding an extra "unnecessary" join
> should help, but would this really have any great effect?
>
> Is this any different in previous versions of Oracle? The consultant
> claims that using this method is better in more recent versions of
> Oracle (I don't know if he is implying that the CBO does this).
>
> Thanks,
>
> CE

cdo is a decremented group, see
http://ourworld.compuserve.com/homepages/joel_garry/oraclech.htm for where you should be asking questions like this.

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

--
_at_home.com is bogus.
http://www.signonsandiego.com/news/computing/personaltech/20030310-9999_mz1b10newsby.html
Received on Tue Mar 11 2003 - 02:41:17 CET

Original text of this message