Re: primary ID trick: speed/disk space?

"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?

> 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 Wed Apr 30 2003 - 21:32:48 CEST