Re: Performance gain by avoiding lookup table join?

From: Jim Kennedy <kennedy-down_with_spammers_at_attbi.com>
Date: Sat, 01 Mar 2003 09:42:15 GMT
Message-ID: <Xf%7a.272873$SD6.14001_at_sccrnsc03>


Why don't you try it? Empirical evidence is worth a lot. Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Charlie Edwards" <charlie3101_at_hotmail.com> 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
Received on Sat Mar 01 2003 - 10:42:15 CET

Original text of this message