|Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
|Date written: 27th Nov 2000
Oracle version(s): 7.3 - 18.104.22.168
|How do I find out how much space a row takes ? What if my row sizes grow over time ?|
Back to index of questions
The easy option is to use the ANALYZE command on a table and investigate the avg_row_len column of the dba_tables or user_tables view. The value given is the average row length, including the 'length byte' for each column, the single byte needed for the 'column count' in each row, and the two byte header for the row. (But excludes the two bytes needed in the row index section of the block).
However, if you do not want to run the analyze command, you can get a reasonable aproximation from the vsize() function. This function tells you the internal storage required for a column - excluding length bytes, so you have to recognise that your result is going to be a little inaccurate.
Consider the scott.emp table
EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
The value nvl(vsize(mgr),0) + 1 will give you the internal storage requirement for the manager column, including the extra one needed for the length-byte, so a reasonable average row size can be calculated with the SQL statement:
select 3 + avg( nvl(vsize(EMPNO),0)+1 + nvl(vsize(ENAME),0)+1 + nvl(vsize(JOB),0)+1 + nvl(vsize(MGR),0)+1 + nvl(vsize(HIREDATE),0)+1 + nvl(vsize(SAL),0)+1 + nvl(vsize(COMM),0)+1 + nvl(vsize(DEPTNO),0)+1 ) from emp ;
Columns holding more than 254 bytes use three-bytes to hold the length of the column. Note-it is the actual length that dictates whether Oracle needs 1 or 3 bytes, not the declared length of the column.
If there are several columns at the end of a row that are all null, then they are not stored at all, and do not need the 1-byte length byte. This is why Oracle keeps a 'column-count' byte at the start of the row.
This code includes the 'column count' and 'row overhead' bytes, but, like the avg_row_len column, ignores the 2 bytes needed by the row's entry in the block row index.
If you are running Oracle 8.1.6, you could use an in-line view with the new SAMPLE clause to apply the complicated expression to a small sample selection of the rows, rather than the whole table.
One of the reasons you might want to ignore the ANALYZE command is that it produces just one figure as the average row size. But some systems don't have such a simple model. You may find that when rows are first inserted into a table they are very short, then they grow over time perhaps in two or three stages.
You can enhance the method above to get an idea of the distribution in sizes of your rows with code like the following, which calculates the length of each row, then groups the row-sizes into buckets to the nearest 20 bytes.:
select row_size, count(*) from ( select 20 * round( ( nvl(vsize(EMPNO),0)+1 + nvl(vsize(ENAME),0)+1 + nvl(vsize(JOB),0)+1 + nvl(vsize(MGR),0)+1 + nvl(vsize(HIREDATE),0)+1 + nvl(vsize(SAL),0)+1 + nvl(vsize(COMM),0)+1 + nvl(vsize(DEPTNO),0)+1 )/ 20 ) row_size from emp ) group by row_size ;
With a more realistic data set, this might give you output like:
ROW_SIZE COUNT(*) --------- --------- 120 47 140 336 160 8437 180 636 200 17 260 239 280 12945 300 1012 320 93
In this example, the data set shows a clear bi-modal pattern - there seem to be two distinct row sizes at around 160 bytes and 280 bytes.
You may not want to apply this code to the entire data set, of course since all those vsize() and nvl() calls are quite CPU intensive. Prior to Oracle 8.1 you might have included a predicate like rownum < 100000 inside the inline view to restrict the volume of data processed; this might skew your results, though, since it is quite likely that you would be looking at the oldest rows, rather than a set selected randomly across time. With 8.1.6 you would probably do better to include the sample clause to select a small fraction of the rows.
Further reading: N/A
Back to top
Back to index of questions