Skip to Main Content
  • Questions
  • Performance on datatype varcahr2(20) vs varchar2(200)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: April 18, 2016 - 2:18 pm UTC

Last updated: April 18, 2016 - 3:03 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I used datatype varchar2(20) for person name. but I also used varchar2(200) in my database.
so what is major difference between varchar2(20) and varchar2(200)?
Can size of the database also impact the performance or not?
at last
how oracle store varchar2 datatype internally like size of the varchar2(200) and i used only 60 then what is the waste space or not?

Thank you Dear.

and Chris said...

The major difference is the maximum length of a varchar2(20) is 20 bytes or characters (depending on how you defined it). The maximum length of a varchar2(200) is 200 bytes/characters:

SQL> create table t (
  2    c20c varchar2(20 char),
  3     c20b varchar2(20 byte),
  4     c200c varchar2(200 char),
  5     c200b varchar2(200 byte)
  6  );

Table created.

SQL>
SQL> insert into t values (null, null, null, null);

1 row created.

SQL>
SQL> update t
  2  set    c20c = lpad('x', 21, 'x');
set    c20c = lpad('x', 21, 'x')
              *
ERROR at line 2:
ORA-12899: value too large for column "CHRIS"."T"."C20C" (actual: 21, maximum: 20)


SQL>
SQL> update t
  2  set    c200c = lpad('x', 21, 'x');

1 row updated.

SQL>
SQL> -- 2-byte character, can only fit 10 chars if column is defined with byte
SQL> update t
  2  set    c20b = lpad('é', 11, 'é');
set    c20b = lpad('é', 11, 'é')
              *
ERROR at line 2:
ORA-12899: value too large for column "CHRIS"."T"."C20B" (actual: 33, maximum: 20)


SQL>
SQL> update t
  2  set    c200b = lpad('é', 11, 'é');

1 row updated.


A varchar2 only uses the space up to the length of the string. So if you store a 1 byte character in a varchar2(200 byte), the other 199 bytes are still available:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:123212348063

There are some gotchas to defining a varchar2 with a large value "just in case". For example, if the sum of the lengths for a multi-column index is too big, you'll get an error:

create table t (
  x varchar2(4000),
  y varchar2(4000),
  z varchar2(4000)
);

create index i on t (x, y, z);

SQL Error: ORA-01450: maximum key length (6398) exceeded


View the maximum length of a varchar2 as a constraint. Ask yourself, should it be possible to store strings longer than X?

If the answer is no, then use that in the definition. e.g. many ISO codes are defined as 2 or 3 character strings. So these should be varchar2(2 char) or varchar2(3 char) as needed.

You can find further discussion and opinions in this Oracle-L thread:

https://www.freelists.org/post/oracle-l/RE-bytes-vs-chars

Is this answer out of date? If it is, please let us know via a Comment