Diff B/W Char, varchar2 & Varchar in Oracle DB

 More Details on Our Website

Char, Varchar and Varchar2 are used to store strings values. But they have major differences let see what they are

Purpose	Char	Varchar2	Varchar
	Char is a data type which is used to store the fixed length character strings.  String values will be space/blank padded before stored on disk. Non Efficient use of space	Varchar2 is used for storing the variable length character strings. Efficient use of Space.	Varchar is just a synonym for varchar2 from (Oracle 9i Onward  its behave like varchar2, but oracle recommend not to use it as in future releases it will be used for another  functionality don’t know when and in which release. Efficient use of Space.
Max length SQL Bytes / Character	2000	4000 In Oracle 12C Release Varchar2 in SQL and PLSQL have same max length 32767	4000
Min Length Bytes / Character	1	1	1
Default length Bytes / Character	1	NA	NA
Max length PLSQL Bytes / Character	32767	32767	32767

.

As per me char or Varchar data types are of no use because there are no as such benefits of Char & Varchar as compare to Varchar2, Char is used by Oracle just for Backward Compatibility and Varchar will be suppose to use for different functionality in Future releases of Oracle.

Generally we use char when you know the data length is fixed... like Flags value like Y, N or gender information like ‘M’,’F’ etc..

But char(1)= varchar2(1) for fixed length values are identical.

Example value of ‘x’ in both fields is same So why to use char  even for fix length values

Drawbacks of Char:

1. More space required on disk as it is fixed length.

2. Not suitable for comparisons with Varchar or varchar2 because char values consist padded blanks and for match we need to use trim or rpad function again this will impact performance.

3. Char is slow in searching as compare to varchar2

See Examples.

Example 1 Space & Padded Blanks test

-- Create table var_char_diff values with name 1 varchar2(5) and name2 char(5) columns

SQL> create table var_char_diff (name1 varchar2(5), name2 char(5));

Table created.

-- insert value ‘AR’ in both name1 and name2

SQL> insert into var_char_diff values ('AR','AR');

1 row created.

-- Check length of name1 and name2

SQL> select length(name1), length(name2) from var_char_diff ;

LENGTH(NAME1) LENGTH(NAME2)  

------------- -------------    

2                 5  

-- Check what is padded for char with dump function

SQL>  select dump(name1),dump(name2), length(name1), length(name2) from var_char_diff ;

DUMP(NAME1)     DUMP(NAME2)    LENGTH(NAME1)    LENGTH(NAME2)

------------- -------------   --------------------------------------------      

Typ=1 Len=2: 65,82     yp=96 Len=5: 65,82,32,32,32    2      5    

-- Chr(32) Is value for the space

SQL> select chr(32) from dual;

C

-

Not convinced use rpad method …

SQL> select replace(name1,’ ‘,’*'),replace(name2,’ ‘,’*') from var_char_diff

REPLACE(CO REPLACE(CO
———- ———-
AR*** AR

Problems occur when you comapre char value with varchar

Work around is to use trim or Rpad when you compare the char and varchar2

SQL> select count(*) from var_char_diff where name1=name2 ;

  COUNT(*) 

----------     

         0

--use trim to cut the leading and trailing spaces    

SQL> select count(*) from var_char_diff where name1=trim(name2) ;

  COUNT(*) 

----------     

         1

  SQL> select * from var_char_diff values where name2= ‘AR’;

Name1 Name2
———- ———-
AR       AR

Here oracle user implicit conversion trims on both sides if data type is char

SQL> variable v1 varchar2(10)

SQL> exec :v1 := ‘AR’

PL/SQL procedure successfully completed.

SQL> select * from var_char_diff values where Name2 = :v1;

no rows selected

Strange what’s going wrong there We are comparing Varchar2 with char

SQL> select * from var_char_diff values where name2 = rpad(:v1,10);

NAME1 NAME2
———- ———-
AR    AR

So you could clearly see the side effect: To get a hit in comparing a character value with a varchar2 variable, you need to use rpad, which appends blank spaces at the end of the variable v1.

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

2. How char is take more space.

create two tables char_t & Varchar_t with one column name in both

One with char(1000) and another with varchar2(1000)

insert 10000 values in both tables same values ‘suppose text ‘arun’ & see the

result.

SQL> create table char_t (name char(2000));

Table created.

SQL> create table varchar_t (name varchar2(2000));

Table created.

SQL> select bytes,blocks,extents from user_segments where segment_name='VARCHAR_T';

     BYTES     BLOCKS    EXTENTS 

---------- ---------- ----------

     65536          8          1    

  

SQL> select bytes,blocks,extents from user_segments where segment_name='CHAR_T';

BYTES     BLOCKS    EXTENTS 

---------- ---------- ----------

     65536          8          1    

--     Block to insert 10000 values     

SQL> begin

      for i in 1..10000 loop

    insert into varchar_t values ('arun');

    end loop;

    end;

    /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

--- Block to insert 10000 values

SQL> begin

    for i in 1..10000 loop

    insert into char_t values ('arun');

    end loop;

    end;

    /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select bytes,blocks,extents from user_segments where segment_name='VARCHAR_T' ;

       BYTES     BLOCKS    EXTENTS 

---------- ---------- ----------      

           196608         24          3          

SQL> select bytes,blocks,extents from user_segments where segment_name='CHAR_T' ;

BYTES     BLOCKS    EXTENTS 

---------- ---------- ----------

  28311552       3456         42   

--See the difference of 3432 blocks & 26Mb more space just for the use of char data type.

   &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

3. Char takes more time in searching as compare to varchar2

SQL> select count(*) from varchar_t;

  COUNT(*)  

----------

10000

Execution Plan

----------------------------------------------------------  

Plan hash value: 1791642866  

------------------------------------------------------------------------  

| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |     

------------------------------------------------------------------------  

|   0 | SELECT STATEMENT   |           |     1 |     6   (0)| 00:00:01 |  

|   1 |  SORT AGGREGATE    |           |     1 |            |          |                      

|   2 |   TABLE ACCESS FULL| VARCHAR_T | 10000 |     6   (0)| 00:00:01 |  

------------------------------------------------------------------------  

SQL> select count(*) from char_t ;

  COUNT(*)   

----------

     10000

Execution Plan

----------------------------------------------------------  

Plan hash value: 4232242706   

------------------------------------------------------------------------  

| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |     

------------------------------------------------------------------------  

|   0 | SELECT STATEMENT   |        |     1 |   748   (1)| 00:00:09 |    

|   1 |  SORT AGGREGATE    |        |     1 |            |          |        

|   2 |   TABLE ACCESS FULL| CHAR_T | 10182 |   748   (1)| 00:00:09 |   

---------------------------------------------------------------------       

SQL> spool off       

08 sec more time in case of char

I always welcome your queries and feedback :)

 More Detail on Our Website