Re: Meaningful surrogate primary keys

From: Nuno Souto <wizofoz2k_at_yahoo.com.au>
Date: 20 Mar 2003 17:17:47 -0800
Message-ID: <73e20c6c.0303201717.de9d692@posting.google.com>

mohsin_nadeem_at_hotmail.com (MnM) wrote in message news:<e9582440.0303201546.73118ed0_at_posting.google.com>...

> Tables (in parent-child sequence): A, B, C
>
> Table A: PK = aa
> Table B: PK = aabb FK = aa
> Table C: PK = aabbcc FK = aabb

This is a complete negation of the advantages of using surrogate keys. You might as well be using normal keys! What you need if indeed you have surrogate keys (what the heck is a "meaningful" surrogate key anyway?) is this:

Table A: PK=aa
Table B: PK=bb FK=aa
Table C: PK=cc FK=bb

and so on.

>
> Now, I want to know, does it effects performance in Oracle when
> surrogate keys are lengthy e.g. 20+ chars?

It can, if your memory resources are limited or you're doing a LOT of index-based I/O.

> If it does, please suggest
> a good solution.
>

Use surrogate keys properly, like above. And use smaller ones, number-based. 10**20 is an AWFULLY BIG number, you don't need that for sure?

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Thu Mar 20 2003 - 19:17:47 CST