Re: Weird advice : keep varchar2 under 255 bytes ???

In article <1113234788.571596.313190_at_o13g2000cwo.googlegroups.com>, jernigam_at_kochind.com says...
>
>Why would you use one bigger if you don't need it?
>
>In Tom Kyte's book he mentions a few reason for not creating bigger
>fields.
>1. Some query tools will format the field based on the size.
>2. Using array fetches will use more memory
>

those are reasons for using the RIGHT size

but not reasons for keeping things artificially small.

Q) what is the only right varchar2 size? A) The right size. Somewhere between 1 and 4000. Use as appropriate for your data

To store a table name from Oracle, varchar2(30) is right. To store a state code, a varchar2(2) is right. To store a free form text description, varchar2(4000) is probably right.

In the book I was advising against using varchar2(4000) for ALL FIELDS regardless. You use the right size in order to have the edits (if it should only be 20 bytes, make it so) and so the client array fetching doesn't have to set up a big bind space.

Say you have a select c1, c2, ..... c10 from t;

C1 .. C10 should have been varchar2(80)
you made them varchar2(4000) instead.

You decide to array fetch 100 rows at a time. The underlying "client buffer" you fetch into will be:

10 columns * 4000+ bytes * 100 rows = 4,000,000

it could have been

10 columns * 80+ bytes * 100 rows = 80,000

all because the client cannot really tell that that varchar2(4000) really meant "80"

The advice is to use the right size for your underlying data.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation