Row length can be calculated by dividing sum of all extent size used by table segment by number of rows in table but this calculation includes amount of space left in extent too which are not used yet. But the actual average row length is the amount of data store in rows of table. ANALYZE command will analyze table and its statistics are store in USER_TABLES. From USER_TABLES we can get average row length of table and this value can used in summarizing resource need for specific table. For example from table below, EMPLOYEES table, resource needed for 1 milllion records will need 217,000,000 bytes (206.94MB) of database resource.
ANALYZE
TABLE employees COMPUTE STATISTICS;
ANALYZE
TABLE job COMPUTE STATISTICS;
ANALYZE
TABLE departments COMPUTE STATISTICS;
SELECT a.table_name, a.num_rows, c.total_bytes sizeof_table,
CASE a.num_rows
WHEN 0 THEN 0 ELSE c.total_bytes / a.num_rows
END arl_wo, a.arl_woo, b.index_len,
a.arl_woo + b.index_len total_arl_woo,
(a.arl_woo + b.index_len
) *
1000000 for_million_records
FROM (SELECT table_name, avg_row_len arl_woo, num_rows
FROM user_tables
) a,
(SELECT table_name,
SUM (column_length
) index_len
FROM user_ind_columns
GROUP BY table_name
) b,
(SELECT SUM (BYTES
) total_bytes, segment_name
AS table_name
FROM user_extents
WHERE segment_type =
'TABLE'
GROUP BY segment_name
) c
WHERE a.table_name = b.table_name
AND a.table_name = c.table_name;
TABLE_NAME NUM_ROWS SIZEOF_TABLE ARL_WO ARL_WOO INDEX_LEN TOTAL_ARL_WOO FOR_MILLION_RECORDS
------------------------------ ---------- ------------ ---------- ---------- ---------- ------------- -------------------
DEPARTMENTS 27 65536 2427.25926 23 44 67 67000000
EMPLOYEES 107 65536 612.485981 71 146 217 217000000
JOBS 19 65536 3449.26316 33 10 43 43000000
Note: All values are in bytes.
NUM_ROW : Number or rows in table
SIZEOF_TABLE: Size of table
ARL_WO: Average row length with overhead
ARL_WOO: Average row length without overhead
INDEX_LEN: Bytes occupied by the index on table for each row
TOTAL_ARL_WOO: Total average row length with index included