Re: varchar2 columns and space question

In article <32FB94B8.4A74_at_healthfare.com>, JJ Reynolds <jjr_at_healthfare.com> writes
>I remember reading somewhere in my Oracle documentation that you should
>always put the fields that are least likely to have data last in your
>table definition because Oracle can make better use of space by trimming
>the row. Is there any truth to this? If you have a two-field table
>creat table mytab (
> field1 varchar2(2000),
> field2 varchar2(2000));
>
>and, LOGICALLY, field1 contains one char of data, will the first
>character in field2 be at position 1 or 2001(base 0 here..) ?
>
>I guess, in short, does oracle only use space actually needed for all
>varchar2 columns, or does it only do this if the following columns have
>no data...?
>

The length of a varchar2 column is the length of the data, plus a length indicator, the length of which (1 or 2 bytes) depends on the length of the column. In your example, field2 would start at position 2 (or thereabouts.

The reasoning behind the suggestion to put nullable columns at the end of the row is that for null columns in the middle of the row, Oracle stores a single byte placeholder, whereas for trailing nulls nothing is stored.

Whether this makes a difference to you will depend on how wide your rows are and how significant the extra single bytes might be.

-- 
Jim Smith