Re: primary ID trick: speed/disk space?

71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0304281107.77b6ddba_at_posting.google.com>...

Thanks for replying...

> 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.)
>
> If "primary key integer ID trick" means that the system constructs a

No, I meant that *I* would create such a key. (Sorry for the confusion.)

That is, I have some tables where there is a candidate key, consisting of a couple of fields, each of which is a string.

So if I use this as a primary key, and then some other table has this as its foreign key, I end up duplicating both strings, again, in another table.

This *could* take up disk space.

If one *explicitly* creates an integer primary key (with AUTOINCREMENT, etc), then this key takes up less space when it appears as a foreign key in another table...

> unique integer based on the physical disk storage, then an extra,
> unverifiable, key is dangerous and only takes up space. The fact that
> such keys can destroy data intgerity is more important than any speed
> you might gain on one version of one release of one SQL product.
>
> And yes, the better designed, modern RDBMS products do use some kind
> of pointers for foreign key constraints. Sybase SQL Anywhere for
> example, puts all the pk-fk constraints into an index structure that
> looks like this:
> (pk_value, pk_ptr, fk1_ptr, fk2_ptr, .., fkN_ptr), so you can
> instantly do multi-table joins on pk-fk relations, test EXISTS()
> predicates, etc. Nucleus uses bit vectors, Teradata has hashing and
> prejoined tables, etc.
>
> That "primary key integer ID trick" is bad programming and a poor
> attempt to return to simple sequential file processing.
Received on Wed Apr 30 2003 - 23:53:45 CEST