Re: primary ID trick: speed/disk space?

stephen.fromm_at_verizon.net (Stephen J. Fromm) wrote in message news:<b4cc5e7c.0304280632.14bdeb6f_at_posting.google.com>...
> Suppose I have a table with a "naturally occuring" key. Does the
> "primary key integer ID trick" add anything in terms of increasing
> speed and decreasing disk space? (Possibly yes: foreign key
> attributes will be an integer. Possibly no: maybe most RDMS's use
> some kind of pointers for fk constraints anyway.)
>
> TIA,
>
> sjfromm

Natural keys are easier to understand however they have one big drawback. If an entire system is built with natural keys the number of conditions in the where clause can reach 20-30 joins/conditions and databases such as SQLServer and Oracle tend to mis-optimize the queries above 15 joins/conditions. More joins/conditions require more work from the database server and creates a slower system. In the accounting tables I worked with, they had 5-6 columns representing the primary key of the table. Joining three tables together which is not unusual required almost 20 join/condition criteria in the select statement.

Serogate keys are easy for the optimizer to parse and require less CPUs when joining, plus it makes updates and deletes easier since the where clause in many cases includes a list of rows or a subquery.

I use Serogate keys about 90% of the time and Natural keys 10% of the time. If the Natural key in a two digit code value like TX for Texas, I may not use a serogate key in that case. If every time I pull an address, I want the abbreviation for Texas, it is nice not to have to add one more table to the join to pull in TX for Texas. Received on Wed Apr 30 2003 - 20:05:11 CEST