# Thread: how to calculate table size?

1. Registered User
Join Date
Apr 2002
Location
USA-CA
Posts
36

## Unanswered: how to calculate table size?

Hi

I have some table XXX.

I need to write some script (input <table name> and <n of rows>)
that let me know how much MB table XXX will allocate
filled out with N rows.

John.

2. Registered User
Join Date
Jul 2003
Posts
2,296
that depends on your extent size

If your initial extent size is 64k, then a table will no rows will take up 64k
in space until you fill up that 64k. Then it will allocate another extent.

My suggestion is to load PRACTICAL (ie: ideal to what the real data will bel) data into the table.
Duplicate to taste and go from there.
Keep in mind varchar2 fields could hold a variable byte amount for each row.

3. Drunkard
Join Date
Nov 2002
Location
Desk, slightly south of keyboard
Posts
697
Hi,

First you need to work out the expected row size. The simplest way is to populate the table with some representative data, then analyse the tables (for all indexes) and run the following....

Code:
```   select table_name,
avg_row_len
from   user_tables

select inds.table_name,
inds.index_name,
sum( inds.sizes ) as index_bytes_per_row
from   (
select i.index_name,
i.table_name,
i.column_name,
decode(data_type, 'DATE'    , 7,
'CHAR'    , data_length,
'VARCHAR2', decode( sign(data_length)-250, -1, .7*data_length+3, .7*data_length+1),
'NUMBER'  , floor(nvl(data_precision,38)/2)+2 ) as sizes
from   user_ind_columns i,
user_tab_columns t
where  t.TABLE_NAME = i.table_name AND
t.COLUMN_NAME = i.COLUMN_NAME
order by i.table_name, i.column_name
) inds
group by inds.table_name, inds.index_name```
This will give you a reasonable estimate of the table size and the index size for each row. This is a good estimate, not a science.

Then you start considering this in the context of the tablespace in which the table is stored and the tablespace in which the indexes are stored.

AIUI if you only specify tablespace names in the storage parameters when creating the table, it will assume initial, next etc from the tablespace storage parameters. It is possible though to specify mismatched storage parameters for the table/tablespace and become quite wasteful on space.

There also other storage parameters such as pctfree etc which have an affect on allocated size and physical disk storage space - I would recommend you look at them separately.

Hth
Bill

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•