Calculating the average row length for rows in a table is done when you analyze the table (using dbms_stats, or automatically in Oracle 10g), but there are times when you need an ad-hoc way to calculate the average row length within an Oracle table, especially when doing capacity planning.
using dbms_lob.getlength(BLOB_COLUMN) to get an accurate average_row_length for rows with a BLOB column..
select
3 + avg(nvl(dbms_lob.getlength(CASE_DATA),0)+1 +
nvl(vsize(CASE_NUMBER ),0)+1 +
nvl(vsize(CASE_DATA_NAME),0)+1 +
nvl(vsize(LASTMOD_TIME_T),0)+1
) "Total bytes per row"
from
arch_case_data
where
case_number = 301;
注：“3”：Row Header。
ad hoc query 即席查詢、突發查詢
ad hoc 即興、隨時出現.
For non cluster tables, the row header is 3 bytes. Each stored row has one row header. One byte is used to store flags, one byte to indicate if the row is locked (for example because it's updated but not commited), and one byte for the column count.
ref: http://www.dba-oracle.com/t_average_row_length.htm
http://www.adp-gmbh.ch/ora/concepts/db_block.html
posted on 2009-04-24 11:39
donnie 閱讀(298)
評論(0) 編輯 收藏 所屬分類:
database