Performance gain by avoiding lookup table join?

Consider the following set-up (8.1.7):

       |
       |
      /|\        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 Thu Feb 27 2003 - 13:03:10 CET