Rod Taylor wrote:
>> If you still declare the natural key(s) as UNIQUEs, you have just made
>> performance worse. Now there are two keys to be checked on UPDATEs and
>> INSERTs, two indexes to be updated, and probably a SEQUENCE too.
>
> Indeed. Using a surrogate key is not free and that is why it would be
> something the DBA would specify during table creation.
>
> The main goal would be to give the option of using a surrogate key
> without being forced to expose it to the applications using the
> database. It is a feature akin to table spaces in that it can help
> performance but without the application or standard users knowing why.
Just this morning my father came to me (he is getting into SQL now that
he is retired) with an issue where a surrogate key probably makes sense.
He is storing a tree of plant families that can get fairly deep. The
primary key is the scientific name. In order to improve performance and
get rid of the recursive lookups he currently does he now wants to use
materialized paths (<parent name>/<sub name>/<sub sub name>). He decided
not to go with nested paths since that makes it very hard to hand fix
things in the tree structure. Obviously using the scientific name in the
materialized paths can quickly give you a really wide column if you have
a fairly deep tree. In that case it could be beneficial to introduce a
surrogate key.
The only annoying bit is that he frequently needs to sync with an
external database where they use no surrogate key so the import slows
down because he needs to check if a surrogate key has been introduced
for every given scientific name before writing to the database.
regards,
Lukas