Re: Why is VARCHAR2(4000) bad ?

On Jan 21, 8: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.

Well I'd probably have reacted badly because

1. you say that the strings are pretty short but have picked the maximum size you could. It suggests a lack of thought about what might go their.

anyway

> Developper:
> - "Its too large, il will impact performance"

well the developer may well have to allocate 4kb of memory for that field.In arrays possibly.

> DBA
> - "When you use the number 4000, you start having all kinds of
> problems, like during transfer" (hugh!)"

I don't really buy that. If you stored stuff as char(4000) then I might.

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

CLOB, which is good for things like file content etc, but do you really not know, or can't restrict by design. Think for example, of Oracle object names. They can't be more than 30 characters long. Users of the application have to abide by that. Mostly we don't complain. Most applications have similar arbitrary (or natural) restrictions on the size of datatypes.

> - Aren't VARCHAR2 supposed to have a first byte to tell the string
> lenght ?
> - Aren't they using only the space they need ?

yes.

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

Consider the implications for both code and design. In addition consider data quality. If I design a table to hold the projects my team are working on then I might use this design

create table project(
name varchar2(20) primary key,
manager varchar2(20) not null,
description varchar2(128)
...
);

and get reasonable descriptions

now try

create table project(
name varchar2(20) primary key,
manager varchar2(20) not null,
description varchar2(4000)
...
);

how good will the descriptions be?

Niall Received on Mon Jan 21 2008 - 15:20:53 CST