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.)

If "primary key integer ID trick" means that the system constructs a 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 Mon Apr 28 2003 - 14:07:32 CDT