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>
>
> 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
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.orgReceived on Mon Jan 28 2008 - 18:55:40 CST