Re: Performance gain by avoiding lookup table join?
Date: Fri, 14 Mar 2003 06:08:42 GMT
Message-ID: <Xns933EBA5E1847dbabobhotmailcom_at_63.240.76.16>
charlie3101_at_hotmail.com (Charlie Edwards) wrote in 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
In the described case, I would have to agree with the consultant, but I could not guess on the actual gain that you would see. The main reason that I have to agree, is that there is only one column in the addr_role_types table. I really don't see the need to have it as a separate table. It is an attribute and not an entity in itself. If however there could be other columns in this table, then consider staying with your current design. Another reason is that a number field will use 22 bytes in your table, but a varchar based upon your shown values will most likely be less, and if you use a char will be exactly the value you size it at, "POSTAL" is only 5 characters, and your field could be that small. In a table with 5 million rows saving 17 bytes per row using oracle's standard %free numbers will result in a savings of over 12,000 blocks (assuming an 8k block size) in the table. So not only will the resulting join be less expensive, but so will a full table scan on the table. Also consider that those 12,000 blocks will use about 100M of your db_block_buffers in the event of a full table scan.
I also am strongly against the use of a numeric key when a natural key exists. The natural key being the actual type code itself. Realize that to implement the addr_role_types table, you not only have to create a primary key on the addr_type_id field, but also a unique constraint on the addr_code field as well, why would you want to do this? Received on Fri Mar 14 2003 - 07:08:42 CET