Re: Why is VARCHAR2(4000) bad ?

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 28 Jan 2008 16:55:40 -0800
Message-ID: <1201568115.713979@bubbleator.drizzle.com>


Andrew M wrote:
> On Jan 26, 12:06 am, DA Morgan <damor..._at_psoug.org> wrote:
>

>> 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:www.juliandyke.com/Presentations/LibraryCacheInternals.ppt
>> specifically page 50.
>> Only four sizes are used. You are forcing the largest and potentially
>> wasting massive amounts of memory.
>>

>
> I disagree. Slide 50 of that presentation does not support anything
> about using more memory because of the size of a column specification.
> It only speaks about the BIND VARIABLE size causing bind variable
> mismatch resulting in multiple child cursors for a statement.
> A SQL statement can query a varchar2(4000) column with a bind variable
> size less than 4000 if needed. There is no extra memory required.
>
> var b1 varchar2(20)
> exec :b1 := '%test%'
>
> select *
> from table
> where col like :b1
> ;
>
> Memory requirements come into play with retrieval of the data
> especially with plsql. Oracle documentation states that any plsql
> varchar2 declared from 1 to 2000 will be preallocated for performance
> and anything larger than 2000 will be dynamically allocated for
> efficient memory use. So the memory that is used for a variable
> depends on the variable definition.
>
> x varchar2(20) -- always uses 20 bytes
> y varchar2(500) -- always uses 500 bytes
> z varchar2(4000) -- dynamically adjusts to what it is set to, but
> is slightly less efficient
>
> I've experimented with this and found it to be almost true. The
> cutoff point seems to be 2001, not the stated 2000. And the
> preallocation does not apply to plsql tables. A plsql table of any
> size is dynamically allocated.
>
> Andrew

DECLARE
  myvar mytable.mycolumn%TYPE;
BEGIN
   myvar := 'A';
END;
/

is best practice.

Want to reconsider?

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Jan 28 2008 - 18:55:40 CST

Original text of this message