Search code examples
oraclesqldatatypes

When to use Char over Varchar2

Is there any benefit of using CHAR over VARCHAR2 ? Specially Oracle database ? As I read around, there is none and actually many recommend using VARCHAR2

What is the difference anyway beside the fixed and varied length ?

Why if there is no benefits from it, is not removed from Oracle at all ?

Solution

  • Already asked and answered

    Oracle datatype: Should I use VARCHAR2 or CHAR

    Just to avoid confusion about much wrong information. Here are some information about difference including performance

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

    Since a char is nothing more than a VARCHAR2 that is blank padded out to the maximum length - that is, the difference between the columns X and Y below:

    create table t ( x varchar2(30), y char(30) ); insert into t (x,y) values ( rpad('a',' ',30), 'a' );

    IS ABSOLUTELY NOTHING, and given that the difference between columns X and Y below:

    insert into t (x,y) values ('a','a')

    is that X consumes 3 bytes (null indicator, leading byte length, 1 byte for 'a') and Y consumes 32 bytes (null indicator, leading byte length, 30 bytes for 'a ' )

    Umm, varchar2 is going to be somewhat "at an advantage performance wise". It helps us NOT AT ALL that char(30) is always 30 bytes - to us, it is simply a varchar2 that is blank padded out to the maximum length. It helps us in processing - ZERO, zilch, zippo.

    Anytime you see anyone say "it is up to 50% faster", and that is it - no example, no science, no facts, no story to back it up - just laugh out loud at them and keep on moving along.

    There are other "made up things" on that page as well, for example:

    "Searching is faster in CHAR as all the strings are stored at a specified position from the each other, the system doesnot have to search for the end of string. Whereas in VARCHAR the system has to first find the end of string and then go for searching."

    FALSE: a char is just a varchar2 blank padded - we do not store strings "at a specified position from each other". We do search for the end of the string - we use a leading byte length to figure things out.