VARCHAR2 1

[elziko]

I'm finding it hard to decide what maximum length to set a VARCHAR2 column to be. Is it very bad to just set it to the maximum (2000) so I don't have to worry to much?

elziko

 

[jimbopalmer]

One consideration, if your users take you up on this, How are you planning to display the 2000 characters on screen and in reports?

My reporting tool, Business Objects, limits columns to 1000 characters, and my user call and whine about it, as they typed all 2000 letters without hitting return.

if you can't display what they typed, don't let them type it! I tried to remain child-like, all I acheived was childish.

 

[karluk]

The only problem I've ever run into with using generous sizes for varchar2 columns is if the column is used in an index. Oracle requires that an index key fit entirely into the same physical block. Unfortunately this calculation is based on the maximum possible size, not the actual length of the existing data. Thus, if your db block size is 2k or 4k, a varchar2(2000) column can never be used in an index.

If you exceed the limit Oracle will return an ORA-1450 error when you try to create the index. The maximum key length for various db block sizes is

ORA-01450 maximum key length (758) exceeded ->(2K Block)
ORA-01450 maximum key length (1578) exceeded ->(4K block)
ORA-01450 maximum key length (3218) exceeded ->(8K Block)
ORA-01450 maximum key length (6498) exceeded ->(16K Block)

 

[rtefft]

Elziko,
If you above considerations are not an issue, go for the larger size. Because it's VARCHAR2 and not CHAR, Oracle will not waste any storage space if you don't use it all.

Karluk - I never knew about the 1-block restriction. Thanks for the info!

Rich ____________________________
Rich Tefft
PL/SQL Programmer