Please advice how to estimate in advance phisycal size of table in 9i
or give a reference on dbf (oracle data file) format. I tried to test
some scenarios but I got quite confused cause even for number fields
size of table in bytes (measured by dba_extents) depends on values in
fields
For example if I create table

create table t_num (t number,t1 number,t2 number,t3 number)

and insert 1048576 of records

insert into t_num values (1078,2000,3001,238)

table size is 24 117 248

if I'm inserting same number of records (definitely I dropping table &
create it again before experiment)

insert into t_num values (1077,2001,3002,237)

table size is 25 427 968

how this can be explained?

dkornyushin@gmail.com wrote:
> Please advice how to estimate in advance phisycal size of table in 9i
> or give a reference on dbf (oracle data file) format. I tried to test
> some scenarios but I got quite confused cause even for number fields
> size of table in bytes (measured by dba_extents) depends on values in
> fields
> For example if I create table
>
> create table t_num (t number,t1 number,t2 number,t3 number)
>
> and insert 1048576 of records
>
> insert into t_num values (1078,2000,3001,238)
>
> table size is 24 117 248
>
> if I'm inserting same number of records (definitely I dropping table &
> create it again before experiment)
>
> insert into t_num values (1077,2001,3002,237)
>
> table size is 25 427 968
>
> how this can be explained?

The calculation is in the docs at http://tahiti.oracle.com: Look it up.

Possibly in 9i but definitely in 10g and above use the DBMS_SPACE
built-in package's CREATE_TABLE_COST procedure.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Jun 23, 1:14 pm, DA Morgan <damor...@psoug.org> wrote:
> dkornyus...@gmail.com wrote:
> > Please advice how to estimate in advance phisycal size of table in 9i
> > or give a reference on dbf (oracle data file) format. I tried to test
> > some scenarios but I got quite confused cause even for number fields
> > size of table in bytes (measured by dba_extents) depends on values in
> > fields
> > For example if I create table
>
> > create table t_num (t number,t1 number,t2 number,t3 number)
>
> > and insert 1048576 of records
>
> > insert into t_num values (1078,2000,3001,238)
>
> > table size is 24 117 248
>
> > if I'm inserting same number of records (definitely I dropping table &
> > create it again before experiment)
>
> > insert into t_num values (1077,2001,3002,237)
>
> > table size is 25 427 968
>
> > how this can be explained?
>
> The calculation is in the docs athttp://tahiti.oracle.com:Look it up.
>
> Possibly in 9i but definitely in 10g and above use the DBMS_SPACE
> built-in package's CREATE_TABLE_COST procedure.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Dkomyous, I would like to point out that dba_extents and dba_segments
show the allocated size of an object so depending on the tablespace
space allocation method chosen: locally managed with uniform extents,
locally managed with auto-allocate extent size, or dictionary
management.

In the absence of valid test data you can use the following logic to
get reasonable estimates for the size of a table:
>>
Oracle Table Sizing Estimation Formula

Abbreviations

AVIL = Available space in block to hold rows
OBS = Oracle block size
RS = Row size
Ovhd = Fixed plus variable block overhead
TBR = Total blocks required

Expected size = (( RS * number of rows) / AVIL ) * OBS) / K or M
where K = 1024 and M = 1048576

Figure RS as
for varchar2 expected number of characters for column
for number where p = number of ditits and s = 0 for positive and 1
for negative
round((( length((p) + s) / 2)) + 1
for date use 7
+ 1 byte per column in row
+ 3 byte row overhead per row
[Or use the dba_tables.avg_row_len value]

Figure number of bytes for block as
pctfree = decimal value of pctfree parameter * OBS

The variable area is mostly made up of 23 bytes per initran area and 2
bytes per row for the row table entry. For 1 to 4 initrans I have
calculated row overhead of 86 to 156 bytes so I just use a constant
for this value. Try 113 to start.

Figure AVIL as OBS - ovhd - pctfree

Total bytes = number of expected rows * RS
TBR = Total Bytes / AVIL
Expected Size = TBR * OBS / 1024 [for K]

This is one way and it is fairly quick and works pretty well. The
formula can be improved by adjusting the variable area size for the
number of initrans and for the number of expected rows in the block,
but using a constant works well for us.
<<

When you have valid test data or some real data and your are trying to
estimate a future size then substitute the actual row length and
overhead that dbms_stats show you via dba_tables.avg_row_len and use
dbms_space as Daniel suggested to get your values.

HTH -- Mark D Powell --

<dkornyushin@gmail.com> a ecrit dans le message de news: 1182615553.542531.118820@p77g2000hsh.googlegroups. com...
| Please advice how to estimate in advance phisycal size of table in 9i
| or give a reference on dbf (oracle data file) format. I tried to test
| some scenarios but I got quite confused cause even for number fields
| size of table in bytes (measured by dba_extents) depends on values in
| fields
| For example if I create table
|
| create table t_num (t number,t1 number,t2 number,t3 number)
|
| and insert 1048576 of records
|
| insert into t_num values (1078,2000,3001,238)
|
| table size is 24 117 248
|
| if I'm inserting same number of records (definitely I dropping table &
| create it again before experiment)
|
| insert into t_num values (1077,2001,3002,237)
|
| table size is 25 427 968
|
| how this can be explained?
|

SQL> select dump(1078), dump(2000),dump(3001),dump(238) from dual;
DUMP(1078) DUMP(2000) DUMP(3001) DUMP(238)
---------------------- ------------------- --------------------- ---------------------
Typ=2 Len=3: 194,11,79 Typ=2 Len=2: 194,21 Typ=2 Len=3: 194,31,2 Typ=2 Len=3: 194,3,39

1 row selected.

SQL> select dump(1077), dump(2001),dump(3002),dump(237) from dual;
DUMP(1077) DUMP(2001) DUMP(3002) DUMP(237)
---------------------- --------------------- --------------------- ---------------------
Typ=2 Len=3: 194,11,78 Typ=2 Len=3: 194,21,2 Typ=2 Len=3: 194,31,3 Typ=2 Len=3: 194,3,38

1 row selected.

Sum the "Len" values.
First row: 11 bytes of data
Second row: 12 bytes of data

Regards
Michel Cadot