Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Change Length of internal representation of datatype Number?
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
6
Typ=2 Len=4: 195,13,35,57
9
Typ=2 Len=6: 197,2,24,46,68,90