| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Size of datatypes in bytes: How to?
A copy of this was sent to "Franz GEIGER" <geigerdi_at_relay.vol.at>
(if that email address didn't require changing)
On Tue, 26 Jan 1999 13:03:32 +0100, you wrote:
>Can anyone tell me, how big are all the datatypes which Oracle provides, or
>gimme a hint where I can look for that info?
>
>E.g. CHAR(32) is 32 bytes, I guess. But what about REAL and NUMBER?
>
>My intention is to be able to tell how many MBs are occupied by a table,
>when I know its CREATE statement and the number of rows it consists of.
>
you need more information then the create and number of rows. you need to ascertain the average row size which is a function of the data VALUES not the datatypes so much.
For example, looking at numbers:
Server concepts manual, chapter 6, section on "Number Datatype" includes this (and much more) about datatypes:
<quote>
Oracle stores numeric data in variable–length format. Each value is
stored in scientific notation, with one byte used to store the exponent
and up to 20 bytes to store the mantissa. (However, there are only 38
digits of precision.) Oracle does not store leading and trailing zeros. For
example, the number 412 is stored in a format similar to 4.12 x 10^2,
with one byte used to store the exponent (2) and two bytes used to
store the three significant digits of the mantissa (4, 1, 2).
<quote>
So, the number 412 in a REAL or a NUMBER will consume the same exact amount of storage (there is only one way to store a number in Oracle -- the NUMBER type).
You can use the VSIZE function to find the storage needed by certain values. for example:
SQL> create table t ( x1 number, y1 real, x2 number, y2 real );
Table created.
SQL>
SQL> declare
2 l_num_str varchar2(25);
3 begin
4 for i in 1 .. 20 loop 5 l_num_str := l_num_str || '1'; 6 insert into t values 7 ( to_number(l_num_str), to_number(l_num_str), 8 to_number( rpad(1,i,'0') ), to_number( rpad(1,i,'0') ) ); 9 end loop;
PL/SQL procedure successfully completed.
SQL>
SQL> select x1, vsize(x1), vsize(y1), x2, vsize(x2), vsize(y2)
2 from t
3 /
X1 VSIZE(X1) VSIZE(Y1) X2 VSIZE(X2) VSIZE(Y2) ---------- ---------- ---------- ---------- ---------- ----------
1 2 2 1 2 2
11 2 2 10 2 2
111 3 3 100 2 2
1111 3 3 1000 2 2
11111 4 4 10000 2 2
111111 4 4 100000 2 2
1111111 5 5 1000000 2 2
11111111 5 5 10000000 2 2
111111111 6 6 100000000 2 2
1111111111 6 6 1000000000 2 2
1.1111E+10 7 7 1.0000E+10 2 2
1.1111E+11 7 7 1.0000E+11 2 2
1.1111E+12 8 8 1.0000E+12 2 2
1.1111E+13 8 8 1.0000E+13 2 2
1.1111E+14 9 9 1.0000E+14 2 2
1.1111E+15 9 9 1.0000E+15 2 2
1.1111E+16 10 10 1.0000E+16 2 2
1.1111E+17 10 10 1.0000E+17 2 2
1.1111E+18 11 11 1.0000E+18 2 2
1.1111E+19 11 11 1.0000E+19 2 2
20 rows selected.
So, as you can see, NUMBER and REAL when they have the same values consume the same space but it depends on the value of the number (not the size of it) upon how much space it will take (the number 111111111 takes more room then 10000000000000 does to store)
you need to load some representative data into the table, analyze it to get the avg_row_len (or use avg( vsize(c1)+vsize(c2)+... ) ) and then plug it into the formula (there is a formula in the admin guide for sizing that goes through this)
>Platform: Oracle 7.3.3 on NT4.0 (Intel)
>
>Thanks in advance and best regards
>Franz GEIGER
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
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 Jan 26 1999 - 07:43:50 CST
 |
 |