Sep 18, 2015

Textual description of firstImageUrl

Analysis of memory usage of CHAR,VARCHAR2 and VARCHAR data type - Difference between CHAR, VARCHAR, VARCHAR2

CHAR and VARCHAR2 are most commonly used data types in Oracle database and PL/SQL program. It is one of the common interview question-  What is difference between varchar and varchar2. The main agenda of this post is to understand the differences between them and learn where do we use them.
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

CREATE TABLE MEMORY_USAGE_TEST (NAME_CHAR CHAR(12) , NAME_VARCHAR2 VARCHAR2(20) , NAME_VARCHAR VARCHAR(20) );
Step 2: Now insert a row in this table and
INSERT INTO MEMORY_USAGE_TEST VALUES('Nikhil', 'Nikhil', 'Nikhil');
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.
SELECT NAME_CHAR , length(NAME_CHAR ), dump(NAME_CHAR ) "ASCII Dump" FROM MEMORY_USAGE_TEST;

SELECT NAME_VARCHAR2 , length(NAME_VARCHAR2 ), dump(NAME_VARCHAR2 ) "ASCII Dump" FROM MEMORY_USAGE_TEST;

SELECT NAME_VARCHAR , length(NAME_VARCHAR ), dump(NAME_VARCHAR ) "ASCII Dump" FROM MEMORY_USAGE_TEST;
After executing the above commands, output appears as shown below:
Now we are in position to compare results of char type and varchar2 type - when select query is executed for CHAR type, dump function gives length of NAME_CAHR as 12 (same as maximum size of that column) even if 'Nikhil' is just 6 character. 'Nikhil' is appended with spaces internally(as indicated by 6's 32 , 32 is ASCII value for space)
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.

 =============== END===============
Read also : How does PL/SQL program executed internally in Oracle Database server?
Location: Hyderabad, Telangana, India