Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Change Length of internal representation of datatype Number?

Re: Change Length of internal representation of datatype Number?

From: <xmark.powell_at_eds.com.x>
Date: 27 Apr 2001 12:17:22 GMT
Message-ID: <9cbo0i$ifq$1@news.netmar.com>

In article <9cbiic$d3cv4$1_at_ID-48479.news.dfncis.de>, Klaus Pittig <pittig_at_web.de> writes:
>Hi.
>
>I'm new to Oracle and I wonder if there is way to reduce the length of the
>internal representation of the datatype number.
>It is 22 bytes long, but I definitely don't need this precision.
>Any help is appreciated.
>
>TIA
> klaus

You can specify the maximum number of digits that will be stored as in number(10) or number(8,2).
The number datatype

Based on the Oracle 8i Concepts Manual chapter on datatypes:

Oracle stores all numeric values in the rdbms in a form of variable length scientific notation with one byte used to store the exponent and up to 20 bytes to store the mantissa. Leading and trailing zeroes are not stored.

The length can be calculated as:
 p = number of digits in value
 s = 0 for positive number and 1 for a negative number

round((( length((p) + s) / 2)) + 1

Here is some SQL to display the length() and stored contents of a number field.

UT1> create table marktest (fld1 varchar2(10), fld2 number, fld3 date); Table created.

UT1> insert into marktest values ('One',123456,sysdate);

1 row created.

UT1> insert into marktest values ('Two',123456789,sysdate - 1);

1 row created.

UT1> select length(fld2) "L", dump(fld2) "DUMP" from marktest;         

L



DUMP

-

         6
Typ=2 Len=4: 195,13,35,57

         9
Typ=2 Len=6: 197,2,24,46,68,90

Received on Fri Apr 27 2001 - 07:17:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US