Re: How long is long enough for primary key ?

krislioe_at_gmail.com wrote:
> 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

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