Re: Primary Key of type string ?

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 31 May 2007 13:41:01 +0200
Message-ID: <5c7qi1F305asiU1@mid.individual.net>

On 31.05.2007 11:26, sybrandb wrote:
> On May 31, 11:03 am, "Oriane" <ori..._at_guermantes.fr> wrote:

>> Just a basic question from a beginner in Oracle. Is is the "standard choice"
>> to use a column of type "int" for the primary key ? I suppose that this is a
>> performance issue ? What about choosing a type "string/char" for a primary
>> key ?

>
> The standard choice is a number(10). Int is a non-existing datatype in
> Oracle and will be automagically translated to number(39).
> Numbers are stored packed, two digits in a byte.
> Choosing string/char is a bad idea.
> First of all, it will take more space, but secondly string comparisons
> are internally more expensive: the string will be compared until the
> first pair of characters that is different.

And I thought the standard choice would be the key dictated by business requirements...

> Do *NOT* store numbers padded with zeroes in a string!
> This will result in much more I/O and all of your code is sensitive to
> implicit type conversion, and Oracle will always convert the database
> column to the variable, thus suppressing index usage.

That's good advice.

Kind regards

        robert Received on Thu May 31 2007 - 06:41:01 CDT