Re: Why is VARCHAR2(4000) bad ?

On Jan 21, 3: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"

FYI, the developer may be thinking of the needs of the host language. If you tell me a column is VARCHAR2(4000), the in my Pro*C program I hace to allocate memory for 4000 characters, plus the size, plus a null indicator variable, all to hold 60 to 180 characters.

What is your Business Requirement??
 Either there is a agreed upon limit, in which case you can create a column with that limit,
 Or there really is not limit (or the limit is > 4000, in which case you can design a CLOB column or possible a text table where multiple rows form the full text.
Until you understand the business need, you canot design this properly.

[]
> Questions:
> - Since we dont know in advance the size we will really need in the
> future, is there a VARCHAR2(*) ?

If you really want an open ended text feature, consider a text table or a CLOB.

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

That's internal to the DB, what do you care how the DB stores VARCHAR2? (You can look it up and see the overhead involved, but it should be irrelevant to designing your table.)

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

Which side should we answer from? internal from the database, or external from the application language?

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

If it really isn't needed there may be application problems. by your arguement, why not size all columns in a table at the max size for the data type? Go back and get the business requirement! then design to the requirement.

   Ed Received on Mon Jan 28 2008 - 21:08:45 CST