Re: How long is long enough for primary key ?

Hi Mr Morgan,
Thank you for your answer.

Our Sales Order :

OrderID : Number(10) -> PK, Surrogate Key CustRefPO : VARCHAR2(30), Unique Constraint => PO numbers from customer.

Yes, we just know that maximum is 30 chars, but there could be variants of length because it comes from customers. And during payment (and also other process the user will refer to this PO Numbers, not the PK.

Would it be performance issue here ?

Thank you,
xtanto

DA Morgan wrote:
> krislioe_at_gmail.com wrote:
> > Hi,
> >
> >> Length is irrelevant.
> > I thougt that the long primary key is bad for performance, isn't it ?
> >
> > And the long PK is the reason for using surrogate key, isn't it.
> >
> > Thank you,
> > xtanto
>
> While it is true that a numeric comparison will probably be faster than
> a string comparison there are many other considerations.
>
> First ... you have no control over the length of a natural key. It is
> what it is.
>
> Second ... if you create a surrogate key you add overhead with the
> creation and use of the surrogate and you create overhead with the then
> required maintenance of a unique constraint and index on the natural
> key too.
>
> I wouldn't lose one minutes sleep over the length of a primary key
> unless I had a specific performance issue that, after optimizing
> everything else, was related to that specific string comparison.
>
> I've seen a lot of slow systems that required tuning. Not once have I
> seen the root cause be the use of a natural key.
> --
> 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 Sat Oct 07 2006 - 23:36:08 CDT