Re: varchar2(size)
From: indytoatl <indytoatl_at_gmail.com>
Date: Wed, 30 Sep 2009 16:47:55 -0700 (PDT)
Message-ID: <1c38c601-8da0-4671-bec7-57e0670668e3_at_v25g2000yqk.googlegroups.com>
On Sep 30, 7:32 pm, Tim X <t..._at_nospam.dev.null> wrote:
> yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) writes:
> > John Hurley (johnbhur..._at_sbcglobal.net) wrote:
> > : On Sep 29, 7:46=A0pm, indytoatl <indyto..._at_gmail.com> 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 -
Date: Wed, 30 Sep 2009 16:47:55 -0700 (PDT)
Message-ID: <1c38c601-8da0-4671-bec7-57e0670668e3_at_v25g2000yqk.googlegroups.com>
On Sep 30, 7:32 pm, Tim X <t..._at_nospam.dev.null> wrote:
> yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) writes:
> > John Hurley (johnbhur..._at_sbcglobal.net) wrote:
> > : On Sep 29, 7:46=A0pm, indytoatl <indyto..._at_gmail.com> 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