By Alexander Geldutes
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:
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
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.
- Analyze statitics for the table.
From SQLPlus run the following command:
ANALYZE TABLE <table_name> COMPUTE STATISTICS;
or
ANALYZE TABLE <table_name> ESTIMATE STATISTICS; - Make select form DBA_TABLES, ALL_TABLES or USER_TABLES dictionary view.
SELECT avg_row_len
FROM dba_tables
WHERE table_name = '<table_name>';
where
avg_row_len - is an average row length (in bytes), including row overhead;= 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)). =
MAX_ROW_LENGTH * (1 + PCTFREE/100) * number_of_rows * 1.15
where 1.15 - just in case.
http://www.dbaoncall.net/references/ht_estimate_table_size.html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29173997/viewspace-1176406/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
