The Oracle (tm) Users' Co-Operative FAQ

How big is a row ? What about rows that grow over time ?


Author's name: Jonathan Lewis

Author's Email: Jonathan@jlcomp.demon.co.uk

Date written: 27th Nov 2000

Oracle version(s): 7.3 - 8.1.7.0

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
;


Approximations:

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