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 ?
> - Aren't they using only the space they need ?
> - Why do I have problems if I use 4000 ?

Others in this thread have mentioned that non-pl/sql software developers might need to allocate the full 4000 bytes because they do not have the options of variable length strings.

But there is another consideration ... 3rd party apps. I use TOAD and apparently TOAD preallocates a fixed length variable to accommodate the 4k varchar2. I have a table with 200 varchar2(4000) columns. TOAD runs out of memory when it tries to retrieve more than one record of this type.

You will never cause a problem by using a varchar2 sized at a reasonable max size. If it is inconceivable that there will be more than 300 characters, then use varchar2(300). This will minimize risk.

It is admirable that you are trying to minimize the risk of having to someday expand that column. However, in don't so, you are exposing yourself to other risks, some of them known and some of the unknown.

The cost of having to expand a column is KNOWN. i.e. we know how to do it, it's very common.
The cost of having to deal with potential problem related to the 4k column is UNKNOWN. Clearly there might be unanticipated problems.

It's better to stick with what's known.

Mike Received on Thu Jan 31 2008 - 13:05:44 CST