Re: Why is VARCHAR2(4000) bad ?

On Jan 21, 12:17 pm, Harel <guh..._at_yahoo.com> wrote:
> I have dimensionned a column to VARCHAR2(4000) in my journalisation
> table. I chose 4000 only because its the maximum allowed for this
> type, and I dont want to be bothered later by people telling me the
> column is not large enough. On the average today this column will
> receive strings of 60 chars, but this could double or triple in the
> future. So I got blasted by one developper, and by one DBA.
>
> Developper:
> - "Its too large, il will impact performance"
> DBA
> - "When you use the number 4000, you start having all kinds of
> problems, like during transfer" (hugh!)"
>
> Questions:
> - Since we dont know in advance the size we will really need in the
> future, is there a VARCHAR2(*) ?
> - Aren't VARCHAR2 supposed to have a first byte to tell the string
> lenght ?

Varies. Search the docs for Length Semantics for Character Datatypes.

> - Aren't they using only the space they need ?

Yes. It may be more than you expect for certain situations, if you think it is the data length plus one byte. Never use 7 byte ascii.

> - Why do I have problems if I use 4000 ?

You will be optimizing for memory usage rather than performance. Search http://tahiti.oracle.com for varchar2(1999) .

jg

--
@home.com is bogus.
"Don't draw fire; it irritates the people around you."