Re: How long is long enough for primary key ?

Quite right. This is sage advice.

If you add a surrogate key, it's vital that you still implement the natural key.

So you are still maintaining a unique index on this column anyway, and have the additional overhead of a primary key on a surrogate.

Also you have to ask: "what will be the source of the surrogate"? the usual answer is: an Oracle sequence. Careful though on that one, using a numeric ascending sequence will cause you concurrent contention issues. If you're a heavily concurrent system and this table will see many inserts you're better off with a "random" unique key.

To put your mind at rest over a 30 byte varchar2 just benchmark it. Build a test table and fill it with 10x the number of records you expect, versus the same table implemented with a numeric key. Test joins, inserts, deletes, updates, concurrent activity. Build some reusable performance benchmarks first. You'll be pleased you did later.

> Refer to my earlier comment. The performance issue must be weighed
> against many factors including the need for an index on the column
> since it is the natural key.
>
> Build the application using the natural key and the chance of that being
> your biggest issue when all is said and done is about as remote as being
> struck by lightening.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Sun Oct 08 2006 - 17:23:30 CDT