Skip to Main Content
  • Questions
  • How do I determine how much storage will be required for NUMBER(p, s)?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Shannon.

Asked: August 05, 2009 - 8:27 pm UTC

Last updated: March 20, 2023 - 4:04 pm UTC

Version: 11.1.0

Viewed 50K+ times! This question is

You Asked

Oracle Database SQL Language Reference states, "Each NUMBER value requires from 1 to 22 bytes." I was curious how much space a NUMBER(10, 0) would take, so I did:

SQL> create table t (a number(10, 0))
  2  /

Table created.

SQL> select column_name, data_length
  2  from user_tab_columns
  3  where table_name = 'T'
  4  /

COLUMN_NAME                    DATA_LENGTH
------------------------------ -----------
A                                       22

I expected that a NUMBER(10, 0) would take less than the full 22 bytes to store. At this point I have three theories about what is going on but have not be able to prove or disprove any to my own satisfaction: 1) NUMBER in both NUMBER and NUMBER(p, s) forms is stored in a variable width format. That storing 1 versus 9999 take different amounts of space on the disk and that storing 1 in a NUMBER(1) column takes just as much space as storing 1 in a NUMBER column. 2) Numbers are stored on the disk in 22 byte chunks, which appears to be contridicted by the documentation. 3) I don't understand what user_tab.columns.data_length means.

Using the DUMP function supports theory 1:

SQL> insert into t values(0);

1 row created.

SQL> insert into t values(9999999999);

1 row created.

SQL> insert into t values(-9999999999);

1 row created.

SQL> select substr(to_char(a), 1, 12), substr(dump(a), 1, 20)
  2  from t
  3  /

SUBSTR(TO_CH SUBSTR(DUMP(A),1,20)
------------ --------------------
0            Typ=2 Len=1: 128
9999999999   Typ=2 Len=6: 197,100
-9999999999  Typ=2 Len=7: 58,2,2,

(The TO_CHAR and SUBSTR are for formatting. Still learning SQL*Plus)

Is dump giving me the on disk representation? If so, then can I find the maximum space takesn by:

select dump(-9999.999) from dual;

Where the number passed to dump is the greatest negative number that fits in number(p, s)? (p nines total, with decimal place correct for the given s)

If not, how do I determine for a given NUMBER(p, s) how many (maximum) bytes is it going to take?

-- Shannon

and Tom said...

All number types are stored as a varying length field from 0 to 22 bytes in length.

From Expert Oracle Database Architecture:

<quote>

It is interesting and useful to note that the NUMBER type is in fact a varying length data type on disk and will consume between 0 and 22 bytes of storage. Many times, programmers consider a numeric datatype to be a fixed length type ¿ that is what they typically see when programming with 2 or 4 byte integers and 4 or 8 byte floats. The Oracle NUMBER type is similar to a varying length character string. We can see what happens with numbers that contain differing amounts of significant digits. We¿ll create a table with two number columns and then populate the first column with many numbers that have 2, 4, 6, ¿ 38 significant digits. Then, we¿ll simply add 1 to each of them:

ops$tkyte@ORA10GR1> create table t ( x number, y number );
Table created.
 
ops$tkyte@ORA10GR1> insert into t ( x )
  2  select to_number(rpad('9',rownum*2,'9'))
  3    from all_objects
  4   where rownum <= 19;
19 rows created.
 
ops$tkyte@ORA10GR1> update t set y = x+1;
19 rows updated.


Now, if we use the built in VSIZE function that shows how much storage the column takes ¿ we can review the size differences between the two numbers in each row:

ops$tkyte@ORA10GR1> select x, y, vsize(x), vsize(y)
  2    from t order by x;
 
         X          Y   VSIZE(X)   VSIZE(Y)
---------- ---------- ---------- ----------
        99        100          2          2
      9999      10000          3          2
    999999    1000000          4          2
  99999999  100000000          5          2
9999999999 1.0000E+10          6          2
1.0000E+12 1.0000E+12          7          2
1.0000E+14 1.0000E+14          8          2
1.0000E+16 1.0000E+16          9          2
1.0000E+18 1.0000E+18         10          2
1.0000E+20 1.0000E+20         11          2
1.0000E+22 1.0000E+22         12          2
1.0000E+24 1.0000E+24         13          2
1.0000E+26 1.0000E+26         14          2
1.0000E+28 1.0000E+28         15          2
1.0000E+30 1.0000E+30         16          2
1.0000E+32 1.0000E+32         17          2
1.0000E+34 1.0000E+34         18          2
1.0000E+36 1.0000E+36         19          2
1.0000E+38 1.0000E+38         20          2
 
19 rows selected.


SQLPlus used its default format ¿ don¿t let the scientific notation confuse you, all of the digits are there, we could use SET NUMFORMAT to see them all. However, we can see that as we added significant digits to X ¿ the amount of storage required took increasingly more room. Every 2 digits added another byte of storage. But a number just one larger consistently took 2 bytes. When Oracle stores a number, it does so by storing as little as it can in order to represent that number. It does this by storing the significant digits, an exponent used to place the decimal place and information regarding the sign of the number (positive or negative). So, the more digits a number contains, the more storage it consumes.

That last fact is why it is useful to know that numbers are stored in varying width fields. When attempting to size a table (to figure out how much storage 1,000,000 rows would need in a table for example), you have to consider the number fields carefully. Will your numbers take 2 bytes or 20 bytes? What is the average size? This makes accurately sizing a table without representative test data very hard, you can get the worst case size and the best case size, but the real size will likely be some value in between.

</quote>


so, your data will consume between 0 and 6 bytes:

ops$tkyte%ORA11GR1> select vsize(x*10+9), vsize(x+1) from (select 999999999 x from dual);

VSIZE(X*10+9) VSIZE(X+1)
------------- ----------
            6          2



Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you.

Shannon Severance, August 06, 2009 - 1:20 pm UTC

That answered my question and then some.

More space required for negative numbers.

Shannon Severance, August 07, 2009 - 3:01 pm UTC

While trying different things to make sure I unstood, I found maximum space of a number(10) is seven bytes, if negative numbers will be stored:

SQL>  select vsize(x*10+9), vsize(x+1), vsize(x*-10+9)
  2  from (select 999999999 x from dual);

VSIZE(X*10+9) VSIZE(X+1) VSIZE(X*-10+9)
------------- ---------- --------------
            6          2              7


Tom Kyte
August 07, 2009 - 3:45 pm UTC

sure, yes.

further reading...

Duke Ganote, August 10, 2009 - 9:48 am UTC

Can you pls clarify this?

Raj, September 19, 2009 - 3:10 am UTC

In this forum it is mentioned that we need to add 1 to value returned by vsize as it does not stores length byte whereas dump function stores it. Can you please clarify the difference?

http://www.orafaq.com/forum/m/422784/73596/#msg_422784

Tom Kyte
September 28, 2009 - 8:24 am UTC

I don't know what you mean by "the dump function stores it"

first - dump is a function, it stores "nothing", it takes inputs and produces outputs and we read the output. It stores nothing.

second - if you look (cut and pasted from orafaq thread you reference):

SQL> select vsize(9),dump(9) from dual;

  VSIZE(9) DUMP(9)
---------- -------------------
         2 Typ=2 Len=2: 193,10

1 row selected.

SQL> select vsize(99),dump(99) from dual;

 VSIZE(99) DUMP(99)
---------- --------------------
         2 Typ=2 Len=2: 193,100

1 row selected.

SQL> select vsize(999),dump(999) from dual;

VSIZE(999) DUMP(999)
---------- -----------------------
         3 Typ=2 Len=3: 194,10,100

1 row selected.



vsize(999) = 3
dump(999) says "length is three"

and the data output for that length of three are the three bytes that go into making up the NUMBER itself. Do you see a length byte there at all? I don't.

Still somewhat unclear

Raj, September 29, 2009 - 12:50 am UTC

I had intended to say if the value "Length" output return by the dump function would also be stored as part of value of the column & require additional 1 byte. I know this may look like, I am kind of making fuss for this additional 1 byte but just wanted to clear my understandings.

i.e If Vsize(999) returns 3 and length shown by dump is also 3 but to store 999 on the disk would require total of 4 bytes i.e 3 bytes for the value,999, itself and additional 1 byte to store the length.


Tom Kyte
October 02, 2009 - 8:12 am UTC

it would actually take more than 4 bytes - because there is a null indicator as well.

vsize is the size of the data
you have to account for the length byte (or two, long strings - 2 bytes)
and the null indicator
and any other overhead we feel like putting in there at the row level.


A reader, September 29, 2009 - 8:44 am UTC

Why there is a difference between the byte size in user segments and sum of vsize of the columns .
Is there are any other factors that skew up the result ( aka difference ).
Please let me know


SQL> create table t as select object_name , object_type , timestamp from all_objects ;

Table created

SQL> Select sum(bytes)/1024/1024 from user_Segments where segment_name ='T';

SUM(BYTES)/1024/1024
--------------------
                   5


SQL> 
SQL> Select  (sum(vsize(object_name)) +  sum(vsize(object_type))  +  sum(vsize(timestamp)) )
  2  /1024/1024
  3  from t;

(SUM(VSIZE(OBJECT_NAME))+SUM(V
------------------------------
               3.4545202255249
               
               
SQL> select * from PRODUCT_COMPONENT_VERSION;

PRODUCT                                                                          VERSION                                                                          STATUS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
NLSRTL                                                                           11.1.0.6.0                                                                       Production
Oracle Database 11g Enterprise Edition                                           11.1.0.6.0                                                                       Production
PL/SQL                                                                           11.1.0.6.0                                                                       Production
TNS for 32-bit Windows:                                                          11.1.0.6.0                                                                       Production
              

Tom Kyte
October 07, 2009 - 6:46 am UTC

vsize shows you the size of a single column - minus the overhead of that column (no length byte is included in vsize, no null indicator)

there are things like

o block overhead, space we use on the block to manage the block
o row overhead, space we use on the block to manage the row in that block
o column overhead, space we use on the block to manage the column in a row on that block


Also, you could insert 1,000,000 rows into a table.
And then delete them all

there would be space allocated in the segment for 1,000,000 rows
but sum(vsize) would return 0 bytes as there is no data in the table right now (free space)


I would not expect these two numbers to be even a little close to each - ever

Formula to determine storage requirements

Karsten, October 01, 2009 - 4:05 am UTC

SQL> select number_col, vsize(number_col) from testtype order by number_col;

NUMBER_COL VSIZE(NUMBER_COL)
---------- -----------------
      -101                 4
      -100                 3
       -99                 3
       -11                 3
       -10                 3
        -9                 3
        -1                 3
         0                 1
         1                 2
         9                 2
        10                 2
        11                 2
        99                 2
       100                 2
       101                 3
       999                 3
      1000                 2
      1001                 3
      9999                 3
     10000                 2
     10001                 4
     99999                 4
    100000                 2
    100001                 4
    999999                 4
   1000000                 2
   1000001                 5
   9999999                 5
  10000000                 2
  10000001                 5
  99999999                 5
 100000000                 2
 100000001                 6
 100000010                 6
 100000100                 5
 100001000                 5
 100010000                 4
 100100000                 4
 101000000                 3
 110000000                 3
 110000001                 6

41 rows selected.

I derived an abstract formula from this:

# of bytes needed = 1 + round_up(# of decimals / 2) - # of trailing pair of zeros [+ optional sign]

I haven't tried to figure out how this translates to Oracle BCDs, especially the "trailing pairs of zeros" stuff. Maybe someone can comment on it...?

A reader, August 16, 2018 - 7:23 am UTC

area 1.0000E+14 how to define area
Connor McDonald
August 18, 2018 - 1:13 pm UTC

Assuming thats the largest you'll go, an appropriately sized NUMBER will do. If you go too small, you'll get an error

SQL> declare
  2    x number(15);
  3  begin
  4    x := 1.0000E+14;
  5* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> declare
  2    x number(12);
  3  begin
  4    x := 1.0000E+14;
  5* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

Over thinking?

Chuck Jolley, August 18, 2018 - 1:41 pm UTC

I thought oracle numbers were stored as base 100 variable length strings for all intents and purposes.
Why not just store it as "number()"? It's not going to take up any more or less space.
Maybe it doesn't work like that now?
And really, unless this column has trillions of rows, why worry about it?

(yes, this is an invitation to discuss storage vs mask)

Connor McDonald
August 20, 2018 - 3:57 am UTC

Space-wise no real difference, but I prefer my database to contain my data to the limits as defined by business requirements.

If someone tries to update a SALARY column to trillions and trillions, I want that operation to fail, because its obviously flawed.

Unless its my salary ... then thats totally cool :-)

Vsize or dbms_lob.getlength

Frj, March 17, 2023 - 4:15 am UTC

With a Blob column, is there a difference between using Vsize or dbms_lob.getlength to work out it's byte size?

If they both come back with the same answer, is vsize supposed to be quicker because there is no context switching? (...and it'll just be interesting to know if their internal workings is different which makes one faster than the other)

Vsize or dbms_lob.getlength

Frj, March 17, 2023 - 7:49 am UTC

Sorry forget that vsize doesn't work for blob

Vsize or dbms_lob.getlength

Frj, March 17, 2023 - 7:59 am UTC

Sorry rather than vsize, I've tried comparing instead LENGTHB Vs dbms_lob.getlength() and consistently I'm getting better performance with LENGTHB, is that expected and if so, why?

Are there scenarios where I should use dbms_lob.getlength instead?

Vsize or dbms_lob.getlength

Frj, March 17, 2023 - 2:10 pm UTC

Ok, now finding lengthb works on characters only, so implicit data type conversion being done on BLOB?

So I just got lucky with lengthb producing the same results and being faster than dbms_lob.getlength() ?
Chris Saxon
March 17, 2023 - 5:24 pm UTC

The LENGTH functions support BLOBs:

The LENGTH function is also supported for the BLOB data type.

https://docs.oracle.com/en/database/oracle/oracle-database/21/adlob/supported-functions-and-operators.html#GUID-10C6706D-CE73-4E21-A2B1-55F11A27A6EF

You can see there's no conversion by checking the predicates in a plan calling LENGTHB on a BLOB:

set serveroutput off
select * from t
where  lengthb( c1 ) > 0;

select * 
from   dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE' );
/*
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T    |
----------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(LENGTHB("C1")>0)
*/


Notice it passes C1 to LENGTHB as-is; no internal function calls, to_blob, etc. So you're safe to use LENGTH on BLOBs

Lengthb or dbms_lob.getlength

Frj, March 18, 2023 - 4:29 am UTC

Thanks Chris. I looked in the obvious but wrong place:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LENGTH.html#GUID-8F97F652-5AE8-4457-AFD7-7A6F25551E0C

...would've thought BLOB would be at least mentioned there.

So, having established LENGTHB works with BLOB, when would you use dbms_lob.getlength() instead on a BLOB? On my system, getlength is proving to be consistently slower than LENGTHB, so does getlength handle something functionally that LENGTHB doesn't?
Chris Saxon
March 20, 2023 - 4:04 pm UTC

Good point; I've raised this with the doc team.

I don't know any particular reasons for preferring dbms_lob; I'm not hugely familiar with LOB though.