Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41

    Unanswered: Oracle Number Datatype, why you need others?

    Sorry if this question has been asked before, I couldn't find it through search.

    Ok, if all numbers in Oracle are stored as numbers, why would you even need others like int, smallint, or decimal etc.?

    Are they just a shortcut? int == number(38)?

    Thanks for any response in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Oracle Number Datatype, why you need others?

    Originally posted by mongoloid001
    Sorry if this question has been asked before, I couldn't find it through search.

    Ok, if all numbers in Oracle are stored as numbers, why would you even need others like int, smallint, or decimal etc.?

    Are they just a shortcut? int == number(38)?

    Thanks for any response in advance!
    They are to provide compatibility with ANSI SQL, and with other databases like DB2. And yes, they all end up as NUMBER.

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    What a coincidence

    Just last night I was thinking about a question quite similar to this one.

    Since numbers have the thing about scale and preicision : NUMBER (4,1) where 4 is the scale and 1 is the precision.
    I was wondering how much space does oracle allocate for any variable declared as a NUMBER.

    In my DB itself there are various sizes of numbers ranging from NUMBER(38) to NUMBER (2). So there is one point, though I am sure Oracle would have itse unique of handling it, but it the space reserved is the same, then space for size 2 is same as space for size 38, it is disk wastage.

    Any information.

    Thanx and Regards
    Aruneesh


    p.s. I am also intrigued by the fact in case of precisions, how the decimal portion stored.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: What a coincidence

    Originally posted by aruneeshsalhotr
    Just last night I was thinking about a question quite similar to this one.

    Since numbers have the thing about scale and preicision : NUMBER (4,1) where 4 is the scale and 1 is the precision.
    I was wondering how much space does oracle allocate for any variable declared as a NUMBER.

    In my DB itself there are various sizes of numbers ranging from NUMBER(38) to NUMBER (2). So there is one point, though I am sure Oracle would have itse unique of handling it, but it the space reserved is the same, then space for size 2 is same as space for size 38, it is disk wastage.

    Any information.

    Thanx and Regards
    Aruneesh


    p.s. I am also intrigued by the fact in case of precisions, how the decimal portion stored.
    The grisly details are all here:

    http://www.ixora.com.au/notes/number_representation.htm

    Basically, NUMBER is a bit like VARCHAR2 - Oracle only uses as many bytes as it requires to store the current value of the NUMBER column in its internal representation.

  5. #5
    Join Date
    May 2003
    Location
    Edmonton Alberta Canada
    Posts
    41
    Oracle store two digits in one byte(there are special cases, like 1, 100, 1000, and 1000000 only take two bytes each).

    I use the following formula:
    for n-digit positive number, Oracle allocates 1+Ceil(n/2) bytes, for n-digit negative number, Oracle allocates 2+Ceil(n/2) bytes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •