Re: varchar2(size)

From: indytoatl <>
Date: Wed, 30 Sep 2009 16:47:55 -0700 (PDT)
Message-ID: <>

On Sep 30, 7:32 pm, Tim X <> wrote:
> (Malcolm Dew-Jones) writes:
> > John Hurley ( wrote:
> > : On Sep 29, 7:46=A0pm, indytoatl <> wrote:
> > : snip
> > : > If I have a table with 200 fields that are all variable length what
> > : > would be the drawback be of making them all varchar2(100) so that I
> > : > don't have to guess a number for each field. The form users fill out
> > : > already has contraints for the same fields (ie, Last Name Field on
> > : > form has 20 space limit.).
> > : Why don't you just make them all 2000 characters apiece since 100
> > : might be too small?
> > : Most properly designed database tables do not have 200 columns in
> > : them.
> > : If the maximum size of a last name is 20 spaces ... why would you not
> > : use varchar2(20)?
> > What if you need to save Mr. Seamus Wolfeschlegelsteinhausenbergerdorff's name in
> > your database?  What if his daughter then gets married and hyphenates her name with
> > her husband's?
> > Many "maximum" sizes are really just arbitrary guesses.
> Well, they are guesses, but they should be based on reasonable
> expectations. While 20 characters might be too small for a surname,
> 2000 characters is getting a little over the top.
> I have two issues with just setting all varchar2 columns in a table to
> something like 250 characters
> 1. It indicates a level of 'lazy' analysis/design. While I think its
> good for developers to be 'lazy' with many things, such as using
> existing code rather than writing new code or using someone elses code
> etc. I think its important to really analyse the requirements for the
> field. It is possible after such analysis, that due to other factors,
> you decide to make all varchar2 250 characters, but this should be a
> deliberate 'active' decision rather than a more passive "Oh well, 250
> chars should be big enough' without any underlying analysis.
> 2. The other issue I have with just making all varchar2 the same large
> value is that I suspect it will potentially have a negative impact on
> storage management. My experience (and everyone differs) is that I like
> to provide as much accurate information to Oracle as possible to enable
> it to make the optimal decisions when managing data storage and
> retrieval and generating execution plans. When defining tables for
> example, I will provide as much accurate details on field types,
> constraints and default values, but I will initially go with database
> defaults for things like extent sizes, percentage of free extents
> tablespaces etc. Only when I know the defults are not optimal do I then
> change them.
> I think the main issue with just defining all varchar2 fields to be 250
> chars regardless of what the real maximum requirements are is that you
> will make storage management sub optimal. Oracle will attempt to manage
> storage in an optimal way, but it needs as much information as you can
> provide to do this well. If you tell it all varchar2 fields could be up
> to 250 chars, then it has to manage storage in a way such that if a
> field is updated from an existing value of 20 characters that it can
> manage the update efficiently when it increases to be 250 characters. >
> this is likely to mean that it will reserve additional space so that if
> the column does increase in size, it doesn't have to 'shift' verything
> along to fit it in. So, if you have 100 columns and they are all defined
> to be varchar2(250, but in reality, they avaerage out at around 20
> chars, they your extents and all I/O operations are likely to be
> sub-optimal.
> The other problem with doing this is that your leaving very little
> information for other developers/maintainers that can guide them in
> maintenance efforts. You also make interface design harder - if your
> creating a web interface for the data, you need to do it in a way that
> the interface will look good, but you have high variation in possible
> field sizes, making this more difficult.
> Tim
> --
> tcross (at) rapttech dot com dot au- Hide quoted text -
> - Show quoted text -

Tim, I replied to John Hurley before I saw your response. Your input is of great
value and I thank you for your time and support. Received on Wed Sep 30 2009 - 18:47:55 CDT

Original text of this message