Re: Key Columns: char vs. number

Every DBA takes into account the precision of a numeric column when calculating the amount of storage space it requires. Each digit of precision takes up approx. half a byte. The values are stored in a variation of BCD - binary coded decimal - which looks very like a string!

Note that NUMBER(10) is an integer and so the precision is known (10 digits) while NUMBER is equivalent to NUMBER(38) - again the precision is known.

JJ

Connor McDonald wrote in message <37723835.5A07_at_yahoo.com>...
>Nuno Souto wrote:
>>
>> This "number better than char for key" thing unfortunately made
>> the rounds a few years ago. Some "experts" who consider
>> that all tables must have a numeric PK and other such pearls of wisdom
>> were given too much importance. Probably their DB2 background
>> made them into "ORACLE experts", although I still fail to see why that
>> should be so...
>>
>> As a result, there is a crop of people around who
>> think these things are in any way true.
>>
>> There is no such thing as numeric fields being better for PK
>> or indexes than char or varchar. Each case has to be examined
>> and the required data type used for PK or index, that's all.
>>
>> BTW, number types are not necessarily fixed length. If you define
>> them as NUMBER(n) with "n" being the precision you want, they do
>> NOT take up 22 bytes! There was an old version of ORACLE where
>> any column defined as NUMBER (without the precision) would always
>> take 22 bytes, but I believe that has been fixed. Nowadays, number
>> columns are variable length as is the length of a number value.
>> Check out the "book" on section data types.
>>
>> --
>> Cheers
>> Nuno Souto
>> nsouto_at_nsw.bigpond.net.au.nospam
>> Is there a nospam domain?
>> http://www.users.bigpond.net.au/the_Den
>> Jens Mayer <jmayer_at_ratundtat.com> wrote in message
>> news:3771df06.6172285_at_news.space.net...
>> > Hi folks,
>> >
>> > yesterday I had a discussion with an Oracle-experienced friend about
>> > the question: is ist better to use CHAR or NUMBER datatypes in Key
>> > Columns, eg. in a Primary Key ?
>> >
>> > Until now I thought that it's better to use CHAR, because NUMBER
>> > takes 22 Bytes of Space in each case. My friend mentioned, that using
>> > NUMBER in Key Columns makes better performance.
>> >
>> > Who is right ?
>
>It makes no difference what precision you specify to the amount of
>storage used for a number....
>
>Of course, its still important to have the precision for validation
>reasons etc etc etc
>--
>===========================================
>Connor McDonald
>"These views mine, no-one elses etc etc"
>connor_mcdonald_at_yahoo.com
>
>"Some days you're the pigeon, and some days you're the statue."
Received on Mon Jun 28 1999 - 17:32:15 CDT