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?
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
In the database, there is no difference. VARCHAR2 are stored with variable length, the declared length is only a limitation.
However, some client applications will reserve 1000 bytes per column instead of 10 if they see that the column is defined as VARCHAR2(1000)
Define the column size as the maximum length you are prepared to deal with. For a staging table, where I'm loading a file into the database, I may use VARCHAR2(4000). Then once all the data is in the database, I can do whatever validations I need to (data types, data lengths, min/max values, acceptable characters...) and have the valid values passed into a column with the appropriate definition/constraints.
I can leverage DML error logging so that data that doesn't fit the definition/constraints gets pumped off to a rejection table without any tricky row-by-row coding.
If you have a VARCHAR2(1000) then, at some point you WILL get data put in there that is longer than you expect (eg you may get a string of 10 characters but which has 14 bytes because some characters are multi-byte character set values.)
