Impact of defining VARCHAR2 column with greater length

Question

What are the effects of defining a column with VARCHAR2(1000) instead of VARCHAR2(10) in Oracle, when the values are not longer than 10 Byte?

Does the column only take the space really necessary to store the values, or would that have any negative impacts on the size/performance of tablespaces/indexes?

Answer 1

The answer depends on whether you're talking about a column in a database table, or a variable in a PL/SQL program.

Database column

The amount of storage used is proportionate to the size of the data stored.

PL/SQL variable

If the variable is declared with a size 1 to 4000 (11g+) / 1999 (10g or earlier), memory will be allocated for the maximum length (i.e. VARCHAR2(100) will require at least 100 bytes of memory).

If the variable is declared with a size 4001 (11g+) / 2000 (10g or earlier) or greater, memory will be allocated according to the size of the data stored. (an interesting side question would be, if the variable's value is changed, how is the memory resized - does it reallocate another buffer with the new size?)

Reference for 10g: PL/SQL Datatypes

Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

Reference for 11g: Avoiding Memory Overhead in PL/SQL Code

Specify a size of more than 4000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed