Skip to Main Content
  • Questions

  • storage of numbers and a shared sql question

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, ted .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: May 20, 2005 - 7:56 am UTC

Version:

Viewed 1000+ times

You Asked

thomas, I have two questions,

1. numeric datatype
the storage is 1+precision/2.
if precision is 9
storage is 6 bytes.
if the field is nullable would 6 bytes allocated
?

2. after oracle idenfying two sqls are identical.
what determines whether oracle is going to reparse
the same sql(resulting in very high parse call/excutions
ratio ?

Thanks again
ted chyn




and Tom said...



No -- a number of precision 9 will not take 6 bytes -- it'll take some number of bytes upto 6.

If you

SQL> create table t ( x number(9) );

and then:

1 declare
2 y varchar2(10);
3 begin
4 for i in 1 .. 10 loop
5 insert into t values (y);
6 y := y||'1';
7 end loop;
8* end;
tkyte@ORACLE> /

PL/SQL procedure successfully completed.

tkyte@ORACLE> select x, vsize(x) from t;

X VSIZE(X)
---------- ----------

1 2
11 2
111 3
1111 3
11111 4
111111 4
1111111 5
11111111 5
111111111 6

10 rows selected.

you'll see that the size of X varies depending on what the data is in X. the precision and scales are EDITS on the data and have nothing to do with the storage (except for putting an upper bound on the size).

A number(9) will consume:

o 0 bytes if it is a trailing NULL column (if no other column after it has a non-null value in that row)

o 1 bytes if it is NULL and not a trailing NULL.

o n bytes according to the formula in the docs. use vsize to see what the number is for a given number.


When we find 2 sqls to be the same (based on a hash) it will be upto the objects it references if we can reuse it. Lets say the SQL we are submitting is:

select * from emp;

lets say the first person to submit that is SCOTT. That is dependent on SCOTT.EMP. Lets say I then run it but I have my OWN emp table. I find scotts hashed query and start to look at it. turns out that if I resolve EMP -- I get TKYTE.EMP, not SCOTT.EMP so I cannot use that parsed query -- I must look further on. So we get the query, we get what the query goes after and make sure that we "see" the same things.


Rating

  (4 ratings)

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

Comments

what am I missing??

Tim Barton, April 22, 2003 - 2:14 pm UTC

Tom,

I'm trying to understand how numbers are stored in Oracle so I can accurately
estimate how much space I need to buy if we add a bunch of numeric columns to
a large table.

I have created the following test and it shows me something
I can't yet explain and it doesn't seem to follow the 1+precision/2 rule.

The get_data function I use here is copied from your One on One book - page 204 -
it is based on dbms_space.unused_space.

I changed the output from your script to

dbms_output.put_line(l_total_bytes-l_unused_bytes || ' total BYTES USED by table');

This is done in Oracle 8.1.7.4.1 with a block_size of 8192 in a
locally managed tablespace with 4m extents.


create table t (d number(10)) pctfree 0;
create table t1 (d number(10)) pctfree 0;

begin
for i in 0 .. 10000
loop
insert into t (d) values (1234);
insert into t1 (d) values (1234567891);
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

TIM> EXEC get_data (t)
131072 total BYTES USED by table

TIM> EXEC get_data (t1)
131072 total BYTES USED by table

What I can't figure is why the tables use exactly the same number of bytes when vsize against the values I inserted return

select vsize(1234) from dual;

VSIZE(1234)
-----------
3

select vsize(1234567891) from dual;

VSIZE(1234567891)
-----------------
6


Any insight???

Thanks,

Tim.


Tom Kyte
April 22, 2003 - 9:36 pm UTC

umm, you forgot about block overhead, row overhead, etc etc etc...

way to size:

o fill a table with representative data
o analyze
o see what it takes
o multiply (if you loaded 1% of the representative data, multiply by 100)

table sizing

reader, May 19, 2005 - 7:46 am UTC

Tom,

By the above method, we get only average row length. We can multiply by the number of rows as per real data volumes. Fine...
How to calculate the size keeping in view the maximum row length?

Can you give any example for this?

Also, like to know the way to calculate the space required for indexes.

Thanks.

Tom Kyte
May 19, 2005 - 8:20 am UTC

only way i know to size:

a) create objects
b) load representative data, some small percent of the real data (could be 0.01% or 10% -- the larger the final set of data the smaller the percent can be)
c) gather statistics
d) multiply


to calculate using maximum length, just use enterprise manager and any tool that offers to size, they do that.



table sizing

reader, May 20, 2005 - 12:17 am UTC 

Tom,

I tried as follows:

SQL> select count(*) from test;

  COUNT(*)
----------
        30

SQL> select table_name,avg_row_len,blocks from user_tables where
  2  table_name='TEST';

TABLE_NAME                     AVG_ROW_LEN     BLOCKS
------------------------------ ----------- ----------
TEST

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select table_name,avg_row_len,blocks from user_tables where
  2  table_name='TEST';

TABLE_NAME                     AVG_ROW_LEN     BLOCKS
------------------------------ ----------- ----------
TEST                              347         10

Like to know why it shows 10 blocks (8k block size)  to store 347 bytes?

If 10 blocks is correct, the requirement is 80k for 30 rows.
For 30000 rows, the requirement would be (80/30)*30000 = 79800 i.e 77.9 mb. Is this correct? Is this inclusive of the space required for the assoicated indexes, if any?

Pl. clarify....

Thanks.

Tom Kyte
May 20, 2005 - 7:56 am UTC 

30*347 bytes.

but you give no real information, like is this ASSM or not.  how was the table created, how many of those blocks are empty, is this a dictionary managed tablespace and that's the extent size...................


ops$tkyte@ORA10GR1> create table t as select rpad('*',347,'*') x from all_objects where rownum <= 30;
 
Table created.
 
ops$tkyte@ORA10GR1> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA10GR1> select avg_row_len, blocks, empty_blocks from user_tables
  2  where table_name = 'T';
 
AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS
----------- ---------- ------------
        353          5            3
 


using non-assm, locally managed tablespace with system allocated extents.


So, I'd guess your extent size is 10*8 blocks, you have empty blocks.

table sizing

reader, May 20, 2005 - 1:15 am UTC

Tom,

Sorry....correction as follows:

db block size is 2k

read as "like to know why it shows 10 blocks (2k block size) to store 347*30 = 10140 bytes?"


Thanks


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library