Re: Why is VARCHAR2(4000) bad ?

From: joel garry <>
Date: Tue, 22 Jan 2008 11:00:12 -0800 (PST)
Message-ID: <>

On Jan 21, 12:17 pm, Harel <> 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"
> - "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 for varchar2(1999) .


-- is bogus.
"Don't draw fire; it irritates the people around you."
Received on Tue Jan 22 2008 - 13:00:12 CST

Original text of this message