Re: primary ID trick: speed/disk space?

"Bob Badour" <bbadour_at_golden.net> wrote in message news:<B8Wra.5$Tx6.2571296_at_mantis.golden.net>...
> "John" <john.lehew_at_hp.com> wrote in message
> news:938581bd.0304301005.45dbcad7_at_posting.google.com...
> > 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.
>
> What, precisely, is the difference between a 'natural' key and any other
> key?

I'm assuming by "natural key" he means a key formed from fields whose meaning is related to the conceptual model behind the database. Likewise, I'm assuming his "surrogate key" is an AUTOINCREMENT integer, which is superfluous in terms of the conceptual model (and superfluous in terms of construction of a primary key, if a "natural" candidate key exists).

>
>
> > 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.
>
> That is a rather astounding claim. How do propose to support the claim?
>
>
> > Serogate keys are easy for the optimizer to parse and require less
> > CPUs when joining
>
> Again, this is an astounding claim. How does the logical interface require
> anything in particular for physical access?
>
>
> >, plus it makes updates and deletes easier since the
> > where clause in many cases includes a list of rows or a subquery.
>
> What does this have to do with the 'type' of candidate key using your
> definition of 'natural key' vs some other type of key?
>
>
> > I use Serogate keys about 90% of the time and Natural keys 10% of the
> > time.
>
> Then you should have no problem defining what a natural key is--you use them
> 10% of the time, after all.
Received on Thu May 01 2003 - 05:03:02 CEST