Re: How long is long enough for primary key ?

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

That would be Purchase Order references, not PO numbers. If they are numbers, store them in a number column.

>
> 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 ?
>

Where?
How do you plan to use this? Do you know how an index scan (binary search) works? Or an index range scan? You will soon notice, there is no theoretical difference between such searches, based on the data type to be searched.

In practice, well, as Daniel Morgan indicated, there are suspicions that numbers compare faster.

I suspect performance differences will be measurable, at best. Not noticeable. Build a test case around it - you should be able to produce results in a matter of minutes.

About 85% of all performance issues I have ever seen, found their roots in a bad design.
Some 10% found their cause in grossly underpowered systems (think parameters, memory assigned, etc) , and the rest was due to bugs.

As a last remark: as PO reference is unique (are you sure? I tend to think Customer/PO Ref would be unique), why not use that for PK? Adding an extra index (PK + UK) will cause extra overhead on inserts, which will affect performance negatively. That is - if you plan on using a unique index (which I think you will)

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...