Re: Does VARCHAR2 size matter?

On Wed, 06 Feb 2002 12:26:47 +0100, Morten <morten_at_kikobu.com> wrote:
>Hi. What consequences are there when using VARCHAR2(4000) rather
>than eg. VARCHAR2(500) columns? I'm wondering as VARCHAR2 is variable
>length, if there are any major performance reasons to keep the
>fields as small as possible, or if those reasons are more likely to
>be from a constraining/modelling perspective.

From:

http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89856/03_types.htm#10805

"Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes."

John

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/