Re: Performance gain by avoiding lookup table join?
From: Frank <fvanbortel_at_netscape.net>
Date: Fri, 14 Mar 2003 19:43:48 +0100
Message-ID: <3E7222E4.3080302_at_netscape.net>
>
>
> 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?
>
Date: Fri, 14 Mar 2003 19:43:48 +0100
Message-ID: <3E7222E4.3080302_at_netscape.net>
Robert Fazio wrote:
> 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?
>
Hmmm, don't quite agree.
It's not really just an attribute. I would say this is a typical implementation of a Designer Super/Sub entity construct.
The denormalized schema proposed (in Designer, that would be
the Discriminator Column approach) would not make it less flexible
to maintain.
To give one example: you can now use the types table to dynamically
fill a drop-down box in the front end GUI. Adding an extra type
is easy, because in a (small) table, and would mean no coding in
the front end.
-- Regards, Frank van BortelReceived on Fri Mar 14 2003 - 19:43:48 CET