Re: Performance gain by avoiding lookup table join?

Charlie Edwards wrote:
> 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

As indicated, did you try it?

I doubt it, as you will probably have queries like '... and addr_code='HOME'
These will now be resolved pretty efficient. If added to the pers_addr_people table, this would create the need for an extra index. Probably two, as an index on addr_code would not be very discriminant.

Any signs that it actually *is* inefficient?

-- 
Regards, Frank van Bortel