Re: Why is VARCHAR2(4000) bad ?

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

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 Received on Mon Jan 28 2008 - 09:53:10 CST