Re: estimation of table size in oracle 9i

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 24 Jun 2007 07:49:36 +0200
Message-ID: <467e05f0$0$25866$426a34cc@news.free.fr>

<dkornyushin_at_gmail.com> a ecrit dans le message de news: 1182615553.542531.118820_at_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 Received on Sun Jun 24 2007 - 00:49:36 CDT