Re: varchar2(size)
Date: Thu, 01 Oct 2009 09:32:51 +1000
Message-ID: <87pr98c8kc.fsf_at_lion.rapttech.com.au>
yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) writes:
> John Hurley (johnbhurley_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
- 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.
- 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 auReceived on Wed Sep 30 2009 - 18:32:51 CDT