| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Number vs Number(x)
As long as the values placed into the number column are the same as you place into the number(3) column, the storage is the same. The formula you reference is the maximum storage that could possibly be needed.
So, as long as the number and number(3) had the same values, they take the same storage. Try this block out, it shows the amount of storage needed to hold numbers of different sizes. As more significant digits are added, the storage requirement goes up. It takes at much room to store 1,000,000,000 as 1 but the number 999,999 takes more...
1 declare
2 str varchar2(50); 3 num number; 4 sz number; 5 begin 6 for i in 1 .. 38 loop 7 str := str || '9'; 8 num := to_number(str); 9 select vsize(num) into sz from dual; 10 dbms_output.put_line( num || ' ' || sz ); 11 end loop;
9999999999999999 9 99999999999999999 10 999999999999999999 10 9999999999999999999 11 99999999999999999999 11 999999999999999999999 12 9999999999999999999999 12 99999999999999999999999 13 999999999999999999999999 13 9999999999999999999999999 14 99999999999999999999999999 14 999999999999999999999999999 15 9999999999999999999999999999 15 99999999999999999999999999999 16 999999999999999999999999999999 16 9999999999999999999999999999999 17 99999999999999999999999999999999 17 999999999999999999999999999999999 18 9999999999999999999999999999999999 18 99999999999999999999999999999999999 19 999999999999999999999999999999999999 19 9999999999999999999999999999999999999 20 99999999999999999999999999999999999999 20
On Tue, 09 Dec 1997 12:38:47 -0600, Jun He <jun.he_at_wcom.com> wrote:
>Storage for number(p,s) is about 1 + floor(p/2) + 1 [+1]
>in Oracle Conceptual Manual.
>
>So number allows for 38 precision 1+ 19 + 1 = 21 bytes. 22 ( if
>negative)
>number(3) alows for only 3. 1+ 1 + 1 = 3 4
>( if negative )
>Storagewise is different. isn't it ?
>
>
>Jun
>
>Thomas Kyte wrote:
>
>> On Tue, 9 Dec 1997 14:21:44 GMT, Douglas Scott
>> <Douglas.S.Scott_at_boeing.com>
>> wrote:
>>
>> >If you are defining a field in a table that will contain integer data
is
>> >it better to define the field with respect to its associated length
(if
>> >max value will be 999 then define field as NUMBER(3)) or to
generically
>> >define it as NUMBER?
>> >
>> >Douglas Scott
>> >Douglas.S.Scott_at_boeing.com
>>
>> Performance wise, storage wise it will have not have an impact if you
>> use NUMBER
>> or NUMBER(3).
>>
>> Application wise, it will make a big difference. NUMBER(3) is similar
>> to
>> creating a table like:
>>
>> create table T1
>> ( x number check ( round(x) between -999 and 999 ) )
>>
>> with a trigger:
>>
>> ...
>> begin
>> :new.x := round(:new.x);
>> end;
>> ...
>>
>> The number(3) will behave like a constraint and a rule, it will
>> - force the value of X to be between -999 and 999 and
>> - to be 3 digits (inserting the value 1.5 into a number(3) returns 2).
>>
>> If that fact is important to your application, or you wish to capture
>> into the
>> data dictionary the fact that the total number of digits is three and
>> it
>> contains no decimal points, then use NUMBER(3).....
>>
>>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Government
>> Bethesda MD
>>
>> http://govt.us.oracle.com/ -- downloadable utilities
>>
>> -----------------------------
>> ----------------------------------------------
>> Opinions are mine and do not necessarily reflect those of Oracle
>> Corporation
>>
>> Anti-Anti Spam Msg: if you want an answer emailed to you,
>> you have to make it easy to get email to you. Any bounced
>> email will be treated the same way i treat SPAM-- I delete it.
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Dec 09 1997 - 00:00:00 CST
 |
 |