Re: Magic of varchar2

Yes, there is a small storage overhead cost increase for declaring a column as varchar2(500) when varchar2(10) would work. Varchar2 columns with a length less than 128 bytes require only a single length/null indicator byte while varchar2 lengths of 128 - 4000 require, I believe, 3 bytes to indicate null and keep track of the length of the data.

A block dump would confirm how many overhead types the column requires.

It is poor design to declare columns with lengths different from what the data should actually be. It the data should not exceed 10 bytes then the insertion of an 11 byte value is an error and should be caught. Defining the column as varchar2(10) would catch this error.

HTH -- Mark D Powell -- Received on Tue Mar 14 2006 - 18:07:47 CST