Re: Key Columns: char vs. number

A copy of this was sent to jmayer_at_ratundtat.com (Jens Mayer) (if that email address didn't require changing) On Thu, 24 Jun 1999 07:39:32 GMT, you wrote:

>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 ?
>
>Jens
>
>
>Jens Mayer
>Rat & Tat Beratungsgesllschaft mbH
>Muehlenkamp 6c
>22303 Hamburg

Server concepts manual, chapter 6, section on "Number Datatype" includes this (and much more) about the number datatype:

<quote>
Oracle stores numeric data in variable–length format. Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. (However, there are only 38 digits of precision.) Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 10^2, with one byte used to store the exponent (2) and two bytes used to store the three significant digits of the mantissa (4, 1, 2). <quote>

Numbers *do not* consume 22bytes a piece. They are stored in about 1/2 byte per significant digit -- the number 10000000 takes less space to hold then the number 99999.

Numbers are not better then chars for keys. Chars are not better then numbers for keys. your mileage may vary.
it depends on the situation/circumstance.

Use the natural key for the table...

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jun 24 1999 - 06:43:46 CDT