CHAR data type is used to store fixed length string and it's maximum size in Oracle database server should be up to 255 bytes.(i.e Maximum 255 characters can be accommodated in a column of a table in given row.).
Fixed length string means - if we store 20 characters string and maximum allowed size is 25, then 5 black spaces will be appended and stored in memory. (waste of memory !!).
Note: Use CHAR type only if you know the sting that will be stored of fixed size, so that memory wastage will be minimum and processing is faster, since it hints optimizer all strings of all are same.
i.e: Index lookup against CHAR field are on average 20% faster than that of VARCHAR fields because of fixed field widths.
VARCHAR2 data type is used to store variable length strings and it's maximum size in Oracle database server should be up to 2000 bytes.(i.e Maximum 2000 characters can be accommodated in a column of a table in given row.).
variable length string means - even if maximum allowed size is 30, if our string is of 25 character, it will just take 25 bytes for storage(and one or two bytes of overhead for length and termination information storage). It is memory efficient if we have varied number of strings that will be stored in that particular column. However, processing of VARCHAR2 is relatively slower than CHAR type.
VARCHAR data type is also used for storing variable strings and maximum allowed characters are 2000. It is subset of VARCHAR2 data type and it is currently synonymous with VARCHAR2.
What is fundamental difference between VARCHAR2 and VARCHAR?
Answer: VARCHAR follows ANSI standard, however VARCHAR2 does not. ANSI standard differentiate between NULL and empty string.
In other words, for VARCHAR data type - NULL and "" is different thing, while for VARCHAR2 both NULL and "" is same.
Note: Oracle recommends to use VARCHAR2 over VARCHAR, since VARCHAR is reserved for future and VARCHAR's meaning might change in a new version of the ANSI SQL standards.
Analyze memory usages by CHAR, VARCHAR2 and VARCHAR :-
Lets create a table with all these data types in oracle database and using dump() function see the memory used internally for storing CHAR, VARCHAR2 and VARCHAR types. I am using Oracle 11g and SQL*PLUS client.Follow the following steps if do not believe in getting just vicarious feelings and execute in any client you have.
Step1: connect to hr schema and create a table using following commands
Step 2: Now insert a row in this table and
Step 3: Now for each columns, we use dump() function to display datatype, values stored and how many bytes it takes for storage: Execute following commands in sequence and compare the results.
After executing the above commands, output appears as shown below:
In second query run, length value is just 6 and no spaces appended here. Similarly, in third run for VARCHAR too, similar result as obtained.
Read also : How does PL/SQL program executed internally in Oracle Database server?