| how to calculate size of table [message #18704] |
Mon, 11 February 2002 11:48  |
Joan
Messages: 36
Registered: February 2002
|
Member |
|
|
How do you calculate size of the table in bytes if you have number of rows to be loaded. eg 1000000.
Thanks in advance
|
|
|
|
| Re: how to calculate size of table [message #18707 is a reply to message #18704] |
Mon, 11 February 2002 18:48  |
Satish Shrikhande
Messages: 167
Registered: October 2001
|
Senior Member |
|
|
Suppose, you have to create a new table and insert some data into it, or table already exists and you will make inserts. You want to estimate the final size of your table, for example you want to set correct storage parameters.
You can do it in two ways:
Using internal statistics.
Math estimation.
It's very rough estimation.
I-st method - using statistics
Analyze statitics for the table.
From SQLPlus run the following command:
ANALYZE TABLE
COMPUTE STATISTICS;
or
ANALYZE TABLE
ESTIMATE STATISTICS;
Make select form DBA_TABLES, ALL_TABLES or USER_TABLES dictionary view.
SELECT avg_row_len
FROM dba_tables
WHERE table_name = '
';
where
avg_row_len - is an average row length (in bytes), including row overhead;
<ESTIMATED_TABLE_SIZE> = avg_row_len * number_of_rows * (1 + PCTFREE/100) * 1.15
where number_of_rows - expected number of rows in you table;
1.15 - just in case :).
NOTE: If it's a new table or it's empty, you may insert some "demo" records before analyzing the table.
II-nd method - using math estimation
Calculate maximum row length of the table based on the following facts:
CHAR( n ), VARCHAR2( n ) - n bytes
DATA - 7 bytes;
NUMBER( p, s ) = floor( (p+1)/2 ) + 1, if it's a negative number - add one byte (+1).
For example, Oracle uses 3 bytes to store number: 512 (NUMBER(3)).
<ESTIMATED_TABLE_SIZE> =
MAX_ROW_LENGTH * (1 + PCTFREE/100) * number_of_rows * 1.15
where 1.15 - just in case :).
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
