Re: Why is VARCHAR2(4000) bad ?

From: Walt <>
Date: Mon, 21 Jan 2008 15:49:06 -0500
Message-ID: <>

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(*) ?

Sort of. It's called a CLOB, and is the better way to deal with arbitrarily long strings. Dealing with CLOBs can be a little tricky since they're proprietary to Oracle and not standard SQL (i.e. you can't expect to use them with ODBC etc.) I wouldn't use a CLOB for this purpose.

My take is that if you're expecting 60 to 180 characters, but don't know the upper limit then picking the max value as something like 3x the max you ever expect would be the way to go. But I don't see a problem with specifying the max of 4000, other than to opine that it wouldn't be a good idea to make this a habit.

The developer doesn't know what he's talking about. The DBA's concerns may or may not be germaine to your situation.

> - Aren't VARCHAR2 supposed to have a first byte to tell the string
> lenght ?

Not really. But you can always query the database for the string length.

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


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

You've got two co-workers wanked off at you. Beyond that I don't see any.

//Walt Received on Mon Jan 21 2008 - 14:49:06 CST

Original text of this message