What Does It Mean When The Size of a Varchar2 in Oracle Is Declared as 1 Byte

What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte?

You can declare columns/variables as varchar2(n CHAR) and varchar2(n byte).

n CHAR means the variable will hold n characters. In multi byte character sets you don't always know how many bytes you want to store, but you do want to garantee the storage of a certain amount of characters.

n bytes means simply the number of bytes you want to store.

varchar is deprecated. Do not use it.
What is the difference between varchar and varchar2?

Difference between BYTE and CHAR in column datatypes

Let us assume the database character set is UTF-8, which is the recommended setting in recent versions of Oracle. In this case, some characters take more than 1 byte to store in the database.

If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

By defining the field as VARCHAR2(11 CHAR) you tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes.

VARCHAR2 datatype in ORACLE

It depends on your Oracle version, but both articles are mostly incorrect.

When you DECLARE the column, you can either declare the stated length EXPLICITLY as either bytes or characters, or IMPLICITLY using your session's default.

Also, the maximum length is 4000 bytes, NOT characters. Even if you declare VARCHAR2(4000 CHAR), the column cannot store more than 4000 BYTES. It will store 4000 characters if they are all single-byte, otherwise it will store fewer than 4000 characters.

Why does Oracle varchar2 have a mandatory size as a definition parameter?

It's the same to define a type of
varchar2(10) or varchar2(1000).

No, it is not the same thing at all.

1. The length of the column is useful metadata for developers building screens.
2. Similarly automatic query tools like TOAD and SQL Developer use the length of the column when they render results.
3. The database uses the length of a variable when allocating memory for PL/SQL collections. As that memory comes out of the PGA supersizing the variable declaration can lead to programs failing because the server has run out of memory.
4. There are similar issues with the declaration of single variables in PL/SQL programs, it is just that collections tend to multiply the problem.
5. Supersized columns create problems for compound indexes. The following is on a database with 8K blocks

....

SQL> create table t23 (col1 varchar2(4000), col2 varchar2(4000))
  2  /

Table created.

SQL> create index t23_i on t23(col1,col2)
  2  /
create index t23_i on t23(col1,col2)
                      *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

SQL>

But above all else, columns sizes are a form of error checking. If the column is supposed to be ten characters long and some autonomic process is trying to load a thousand characters then something is wrong. The process ought to fail, so we can investigate why we are loading duff data. The alternative is a database full of garbage, and if that is what was wanted we should just have given everybody Excel and have done with it.

It is true that changing the column size when it turns out we have underestimated can be tiresome. But it doesn't happen very often, and we can mitigate a lot of the pain by using %TYPE and SUBTYPE declarations in our PL/SQL instead of hard-coding variable lengths.

---

"why no such declaration in NUMBER type"

Numbers are different. For a start, the maximum size of a number is much smaller than the text equivalent (38 digits of guaranteed precision).

But the key difference is that Oracle stores numeric values in scientific notation so there is not a straightforward relationship between the arithmetical size of the number and the storage space it consumes.

SQL> select vsize(123456789012345678901) n1
  2         , vsize(999999999999999999999999999999) n2
  3         , vsize(0.000000000000000000001) n3
  4         , vsize(1000000000000000000000000) n4
  5  from dual
  6  /

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        12         16          2          2

SQL> 

Nevertheless, it remains good practice to specify scale and precision wherever possible, especially when we are dealing with integers, say, or money.

Impact of defining VARCHAR2 column with greater length

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

What is the difference between varchar and varchar2 in Oracle?

As for now, they are synonyms.

VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes.

VARCHAR2 does not distinguish between a NULL and empty string, and never will.

If you rely on empty string and NULL being the same thing, you should use VARCHAR2.

---

Related Topics