Re: primary ID trick: speed/disk space?
Date: 28 Apr 2003 12:07:32 -0700
Message-ID: <c0d87ec0.0304281107.77b6ddba_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.)
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:
That "primary key integer ID trick" is bad programming and a poor
(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.