Re: Table sizing question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/08
Message-ID: <339cbcc4.8600526@newshost>#1/1

On Sat, 07 Jun 1997 17:55:14 -0500, Bob B <bobo_at_cyberramp.net> wrote:

>The definition of a "NUMBER" says that it can occupy up to 21 bytes.
>
>What are the acctual column sizes (number of bytes required) for
>something like
>
>NUMBER(5) (a short integer range)
>NUMBER(10) (an integer range)
>NUMBER(14.7) (a float range)
>NUMBER(22.15) (a double range)
>
>And what gave you that information?
>

It all depends on what number you put in there. You can use vsize to see the amount of storage allocated by some column/variable in the database. for example:

SQL> set numformat 9999999999.999999999999999 SQL> create table nums
2 ( num5 number(5),

  3      num10    number(10),
  4      num14_7    number(14,7),
  5      num22_15 number(22,15)

6 )
7 /

Table created.

SQL> desc nums

 Name                            Null?    Type
 ------------------------------- -------- ----
 NUM5                                     NUMBER(5)
 NUM10                                    NUMBER(10)
 NUM14_7                                  NUMBER(14,7)
 NUM22_15                                 NUMBER(22,15)

SQL>
SQL> insert into nums values ( 1, 1, 1, 1 );

1 row created.

SQL> insert into nums values ( 10000, 1000000000, 100000.000000,

  2                              1000000.0000000000000);

1 row created.

SQL> insert into nums values ( 99999, 9999999999, 999999.999999,

  2                              9999999.9999999999999);

1 row created.

SQL>
SQL> select num5, vsize(num5) from nums;

                       NUM5                 VSIZE(NUM5)
--------------------------- ---------------------------
          1.000000000000000           2.000000000000000
      10000.000000000000000           2.000000000000000
      99999.000000000000000           4.000000000000000
 
SQL> select       num10, vsize(num10) from nums;
 
                      NUM10                VSIZE(NUM10)
--------------------------- ---------------------------
          1.000000000000000           2.000000000000000
 1000000000.000000000000000           2.000000000000000
 9999999999.000000000000000           6.000000000000000
 
SQL> select       num14_7, vsize(num14_7) from nums;
 
                    NUM14_7              VSIZE(NUM14_7)
--------------------------- ---------------------------
          1.000000000000000           2.000000000000000
     100000.000000000000000           2.000000000000000
     999999.999999000000000           7.000000000000000
 
SQL> select       num22_15, vsize(num22_15) from nums;
 
                   NUM22_15             VSIZE(NUM22_15)
--------------------------- ---------------------------
          1.000000000000000           2.000000000000000
    1000000.000000000000000           2.000000000000000
    9999999.999999999999900          12.000000000000000

So the amount of storage for a given number column will be dependent on the number stored within.... 1 takes less space the 12345 in the same column.

>
>Thanks
>
>BB

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 Received on Sun Jun 08 1997 - 00:00:00 CDT