Re: Why is VARCHAR2(4000) bad ?

From: DA Morgan <>
Date: Fri, 25 Jan 2008 21:06:32 -0800
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.

I'd blast you too but I'd do so for a very different reason.

  1. You are showing a complete lack of concern about data integrity.
  2. When you load that VARCHAR2(4000) into memory it will require a larger space than would a properly sized VARCHAR2. Check out this presentation by Julian Dyke: specifically page 50. Only four sizes are used. You are forcing the largest and potentially wasting massive amounts of memory.
  3. You are showing a complete lack of concern about data integrity.

Keep it up and the Report Writers will likely want to tar and feather you. They love trying to format oversize data fields because someone was too lazy to do it right.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Fri Jan 25 2008 - 23:06:32 CST

Original text of this message