Storage size(s) for the NUMBER data type 1

[kaikaufmann]

Could someone please tell me what the actual byte count is for the NUMBER data type. I am trying to come up with a database sizing tool and need to know the following byte counts for the following declarations:

NUMBER(3)
NUMBER(5)
NUMBER(10)

I know that the maximum storage size is 21 bytes, but doesn't Oracle use significantly less space when the precision and scale are limited?

Any help is greatly appreciated,

Kai

 

[sem]

Oracle doesn't store number in fixed-length fields. You may obtain the size of your NUMBER field by selecting DUMP. As for NUMBER(3), 3 is used only for validation and has no affect on storage.

Regards, Dima

 

[SantaMufasa]

Kai,

Just to add to Dima's observations: Oracle stores all numeric data in the same fashion, with a sign, significant digits, and exponent. Each significant digit occupies a half-byte. When you say, "Number(10)", it means that the data you store will be integer up to 10 digits, but that does not pre-determine the number of storage bytes for any specific value. For example, if you store the number "1", it occupies two bytes:

SQL> select dump(1) from dual;

DUMP(1)
------------------
Typ=2 Len=2: 193,2

If you store the number 9000000000, it still occupies only two bytes, even though it is 10 digits long:

SQL> select dump(9000000000) from dual;

DUMP(9000000000)
-------------------
Typ=2 Len=2: 197,91

If, however you stored a number with 10 explicit digits, then your storage consumption is six bytes:

SQL> select dump(9999999999) from dual;

DUMP(9999999999)
------------------------------------
Typ=2 Len=6: 197,100,100,100,100,100

Note that a 50-digit number can occupy as little as two bytes:

SQL> select dump(10000000000000000000000000000000000000000000000000) from dual;

DUMP(10000000000000
-------------------
Typ=2 Len=2: 217,11

Cheers,

Dave

 

[kaikaufmann]

Thanks for both of your answers. This brings up another question though. Say I populate a column with the number 1 in which case it will occupy 2 bytes in the database. If I later update that value to be 123456 which requires more storage space, what does Oracle do to allocate more space. Does it fragment the columns storage area, or does it fully allocate a new block?

Thanks again,

Kai

 

[SantaMufasa]

Kai,

Good question. Oracle accommodates updates using the following triage:

1) If there is enough freespace in the existing block to accommodate the update, it "Pushes" the rest of the data in the block "downward" into the free space to make room for the expanded field.

2) If there is not enough space in the existing block to accommodate the update, then Oracle &quot;chains&quot; the row by writing the row data into a new block, leaving behind a &quot;forwarding address&quot; of the row's new block location. This means that any index reference can stay the same, since the index still points to the original block, then once the server reaches that block, it then forwards to the chained block. Obviously, this extra jump takes CPU cycles, so when an appreciable amount of chaining occurs, then you can remarkably quickly defrag the table with the command: &quot;ALTER TABLE <tablename> MOVE PARALLEL NOLOGGING;&quot; which reorganizes the table at a rate of about 6MB+/sec (depending upon your system)

Dave

 

[fmorel]

Great Dave !

It is always useful to read tech-tips ! I thought I knew a good way to unchain rows (analyze table list chain rows into..), but I did not know the &quot;MOVE&quot; trick...

Thanks a lot!

 

[kaikaufmann]

So far all of the information is very useful for me to develop a sizing tool. But the question is, how do estimate sizes columns with a data type of NUMBER. Do you have to rely on what you know about the nature of the data?

For example, if I have a table that I don't expect to grow more than 1000 records and is defined as a NUMBER(5) data type, then do I just declare an average size of 3 bytes.

Or, if I have a table that will possibly contain millions of records with a data type of NUMBER(10) do I just use the maximum byte size of 6 (is this even correct?).

I guess I'm curious about standard growth prediction practices on an oracle database.

Once again, any help is greatly appreciated,

Kai

 

[sem]

For NUMBER(10) you obviously should reserve 6 bytes. But are you really going to estimate requirements with 1% precision? Does it really matter whether your table takes 3K or 10K ??? I suppose that row numbers expectations are more important: is it worth to claculate one factor with 1% tolerance, if only order of magnitude at best case is known for another?

Regards, Dima

 

[SantaMufasa]

As usual, I totally agree with Dima: How important is it to your organization that you predict storage consumption for your data (especially numbers since they are not the usual storage-consumption monster). If my customers made me predict storage consumption at that granularity, I probably would not have time to read Tek-Tips. With the prices of disk storage as relatively cheap as they are (compared to the cost of say, software development/acquisition), I recommend that you not spend much effort on trying to precisely predict space consumption of numbers (which, as we've shown, is generally, in bytes, about 60% of the number of significant digits).

If you must come up with predictive numbers, then an easier method is to &quot;ANALYZE TABLE <tablename> COMPUTE STATISTICS;&quot; on tables with fairly representative production data, then look at the average row length in the USER_(or ALL_, or DBA_)TABLES, then extrapolate that size times the expectec row-number growth over your predictive period. Also remember that your indexes need similar sizing attention.

But again, don't try to be precise about an estimate that has a predictive-error factor of +/- 30% or more.

Those are my two-cents/pence/peso worth.

Dave

 

[sem]

My own experience says that even the most pessimistic estimations in real life should be multuplied by pi

Regards, Dima