Re: NULLS

JP Soria <jpsoria_at_saxe.com> wrote in article <331F0DFB.F8_at_saxe.com>...
> How much space does a null take?
>
> For example:
> I have a field, datatype varchar2(10), if this field is null for one
> row, how many bytes does it use?
> --
> JP Soria
> Jr. Oracle7 DBA
> Email: jpsoria_at_saxe.com
> "Failing to plan = planning to fail"
>

In reply:

The answer depends on where your column (VARCHAR2(20)) is located in the table (let's say called 'table1') column ordering.

Let's say your column (field in your terms) is named 'col3'.

1. If col3 is at the very end of the table (most efficient)e.g.

table1 (col1 NOT NULL number

	,col2 NULL	varchar2
	,col3 NULL	varchar2(20))

then if col3 IS NULL it will take up no space at all.

2. If col3 is in the middle of the table where the column proceding can also be NULL (efficient) e.g.:  

table1 (col1 NOT NULL number

	,col2 NULL	varchar2
	,col3 NULL	varchar2(20)
	,col4 NULL	number(3,0)
	,col5 NULL	vanrchar(12))

then if the columns proceding have NULL values then col3 (and the proceding columns) will take up no space. If one of the columns proceding has a value, then col3 takes up 1byte (to indicate it's it's there).

3. If col3 is in the middle of the table where a column proceding is defined as NOT NULL (inefficient) e.g.:

table1 (col1 NOT NULL number

	,col2 NULL	varchar2
	,col3 NULL	varchar2(20)
	,col4 NULL	number(3,0)
	,col5 NOT NULL	varchar2(12))

Then col3 will ALWAYS take up 1 byte (as will col4 in the above case) as a minimum even when it IS NULL.

Therefore, it is always best practice, when designing tables to cluster all NOT NULL columns at the top of the table definitions and avoid adding NOT NULL columns to the end of a table definition at a later date.

Hope this has helped you. Good luck!

Martyn Cavett
Senior ORACLE Analyst
home: cavett_at_globalnet.co.uk
work: Martyn.Cavett_at_gtplc.com Received on Sat Mar 08 1997 - 00:00:00 CST