Use of CHAR vs. VARCHAR2
Lynn Hopkins, August 08, 2001 - 2:16 pm UTC
Exactly the information I was looking for.
shoud I use char or varchar2
A reader, February 03, 2003 - 7:58 pm UTC
A reader, December 01, 2003 - 4:29 pm UTC
reader
A reader, December 29, 2003 - 9:25 am UTC
char or varchar - thank you
Courtland, May 11, 2005 - 10:50 am UTC
This is the information I was looking for; it came up as hit #2 in the Google search "oracle datatypes char varchar varchar2 site:oracle.com".
good one
Vijay, December 08, 2005 - 7:07 am UTC
Hi Tom,
good day to you, hope you are doing good, just to know out of curosity what is the reason to have CHAR datatype when we have Varchar2, other than for backward compatibility.
Kind Regards,
Vijay
December 08, 2005 - 7:13 am UTC
if you ask me....
none. Here is an excerpt from my most recent book on that topic:
<quote>
The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot find their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed. ......
</quote>
Some reasons why CHAR is commonly used
Adam Musch, December 08, 2005 - 8:03 am UTC
1. Some programming languages need to pull both data and data length from the database when using cursors against variable-length columns. Therefore, programmers may prefer CHARs, as they then have to write less code.
(Sure, it's specifically COBOL, but some of us still support apps written in COBOL.)
2. Some database engines have a bias for CHAR columns, with "myth and legend" tuning stating that "CHAR columns perform better." Therefore, a portable application (I know, foolishness itself) may use CHARs more frequently, even if a VARCHAR2 would be a more logical choice.
I agree with using VARCHAR2's, however. Just pointing out some reasons why one might go in another direction.
trim
karthick, December 08, 2005 - 10:20 am UTC
select * from t where trim(x) = :y
this also works but if there is an index on x the index is not considered why so.
December 08, 2005 - 11:52 am UTC
because you applied a function to the column X that modifies the value of X, the index on X has the value of X not the modified value of X.
University SQL Help
Tim, November 06, 2006 - 1:36 am UTC
Very helpful page. Now I'm positive my Prof is an idiot. Thanks for reassuring me.
advantage of varchar than varchar2
Hanumantha Rao, November 14, 2006 - 8:04 am UTC
hi,
this is Hanuma from Mumbai, India. In our project we are using varchar instead of varchar2. i don't know, what is the advantage of varchar? plz clear my dought?
November 14, 2006 - 8:10 am UTC
A reader, November 14, 2006 - 8:39 am UTC
When to use CHAR vs. VARCHAR2
Robert Simpson, November 14, 2006 - 11:05 am UTC
As previously indicated, CHAR and VARCHAR2 are stored identically in the database, so the determination of whether to use CHAR or VARCHAR2 is more closely tied to the programming language and practices than to the database. The difference is that comparisons of two CHAR fields will be automatically blank-padded; comparisons with VARCHAR2 will not. For an example of what NOT to do, we had an application where the DBAs' recommendation was overridden, and all database columns were made VARCHAR2, in order to save time on the design. As a result, every time one of these columns is compared to a CHAR, the programmers must use an RPAD or RTRIM function, since PL/SQL won't do automatic padding on VARCHAR2s. In some cases, when the function is applied to an index column, the query runs much longer because the index is not used (I do realize that you could create a function index, but we usually fix the problem by moving the function to the other side of the "="). So the time that was saved in the design phase has been spent many times over trying to determine why certain queries are running so poorly.
One reason that VARCHAR2 is often used is that some programmers use variable length character variables, which will also defeat the programming language's automatic padding for fixed-length comparisons. For example, in Java, programmers might simply use "String", which is variable length, for all character variables and avoid "char[]", which is the appropriate type for storing data being transferred to or from a database CHAR column.
In database design, we're taught how to determine whether to make a column CHAR or VARCHAR/VARCHAR2. You ask yourself, "is the length of the value significant?" For example, if the column has up to 6 characters representing a gender, is a 6-character "MALE " different from a 4-character "MALE"? In this case the answer is obviously "no" and the column should therefore be CHAR so that regardless of whether you compare it to a 4-character constant or 6-character host variable, the comparison is automatically padded and works properly regardless of whether the column contains "MALE " or "FEMALE" and whether the constant or host variable contains "MALE", "MALE " or "FEMALE". If either side of the comparison is a variable length character type, then you will need to RPAD and/or RTRIM one or both sides of the comparison to make it work properly.
November 15, 2006 - 6:38 am UTC
if you just use varchar2 everywhere, no problem
My advice is, has been, will be very loudly stated as:
IGNORE THE EXISTENCE OF CHAR.
period. If you never use char, you never compare char to varchar2, problem solved.
And if you use char and compare a char(n) to a char(m) they will never compare either.
Just say NO TO CHAR.
char / varchar2 storage
Mark A. Williams, November 14, 2006 - 9:21 pm UTC
Hi Robert,
You said, "As previously indicated, CHAR and VARCHAR2 are stored identically in the database, so the determination of whether to use CHAR or VARCHAR2 is more closely tied to the programming language and practices than to the database."
I think there might be a qualifier missing in that statement. CHAR and VARCHAR2 are stored identically when they are the same length *and* when that length is equal to the size specified for the column.
In any case, here's example:
SQL> connect /
Connected.
SQL> create table char_test
2 (
3 c1 char(8),
4 c2 varchar2(8)
5 );
Table created.
SQL> insert into char_test values ('a', 'b');
1 row created.
SQL> commit;
Commit complete.
SQL> @get_header_block
Enter segment name: char_test
HEADER_FILE HEADER_BLOCK
----------- ------------
5 9
1 row selected.
SQL> @dump_block
Enter data file name or number: 5
Enter min block: 10
Enter max block: 10
System altered.
SQL>
Now looking at the relevant part of the block dump:
col 0: [ 8] 61 20 20 20 20 20 20 20
col 1: [ 1] 62
It does not seem that the CHAR and VARCHAR2 columns are stored identically in the database.
Also, "For an example of what NOT to do, we had an application where the DBAs' recommendation was overridden, and all database columns were made VARCHAR2, in order to save time on the design."
You don't mean to say that a column that might have been declared as varchar2(32) was made char(32) do you?
Perhaps I've misunderstood what you are saying, and, if so, I apologize.
- Mark
My poor wording
Mark A. Williams, November 14, 2006 - 10:48 pm UTC
I wrote:
You don't mean to say that a column that might have been declared as varchar2(32) was made char(32) do you?
What I meant:
The DBAs recommended something like char(32) and someone else decided to make it varchar2(32)?
- Mark
CHAR vs. VARCHAR in database
Robert Simpson, November 16, 2006 - 4:59 pm UTC
I suspect the original author was referring to the way they are stored with a length followed by the value of the column. See his example above, just under "and we said...".
As far as qualifiers go, this is only the way that Oracle works. In DB2, for example, a CHAR takes less space to store than a VARCHAR, and potentially a _lot_ less space in the logs, since every column after the VARCHAR must be included in the log records if the length of the VARCHAR changes (they recommend putting VARCHARs at the end of rows for that reason).
The DBAs recommended making each column whatever it should be, whether that be CHAR or VARCHAR, based on good database design principles (see my "gender" example above). It was decided not to spend the time to do that, so instead we've had to spend more time determining whether or not you need to add RPADs and RTRIMs and solving the other problems that approach causes when those functions are put on the wrong side of the "=". Obviously, it's much easier to use CHAR and let the padding happen automatically. (You may still need to be careful about which side of the "=" is going to be automatically padded - I'm not sure if, when a key column get padded, if it can still use the index or not. But, adside from typos, you should be comparing only to shorter values, for example: GENDER = 'MALE', but not GENDER = 'UNKNOWN' or GENDER = 'FEMALE ' when GENDER is CHAR(6).)
Answered our exact question, thanks!
User Erik, November 17, 2006 - 4:01 pm UTC
A reader, December 19, 2006 - 8:52 am UTC
Different behavior of LIKE since 9i
Uwe M. Kuechler, January 22, 2008 - 10:06 am UTC
Dear Tom,
my customer has been aware of this issue for quite some time, yet still some CHAR columns can be found in legacy (and third party) tables. Recently, I've found an interesting behavior of the LIKE operator that changed after migration to 10g. I wonder If you have some background on this?
Using your example in 8i:
SQL> create table t ( x char(10) );
Table created.
SQL> insert into t values ( 'Hello' );
1 row created.
SQL> select * from t where x LIKE 'Hello';
X
----------
Hello
Now, let's try the same in 9i and above:
SQL> select * from t where x LIKE 'Hello';
no rows selected
Of course I recommended to migrate from CHAR to VARCHAR2 or use LIKE ...% at least, but I'm still curious if Oracle changed the internal optimization from LIKE to "=".
Best regards,
Uwe
January 22, 2008 - 6:27 pm UTC
it was a "bug" in 9i, it was not promoting the 'hello' literal to a char for the like, 10g does.
it is still "like" in 10g:
ops$tkyte%ORA10GR2> select * from t where x like 'Hello';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01
|* 1 | TABLE ACCESS FULL| T | 1 | 12 | 3 (0)| 00:00:01
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X" LIKE 'Hello')
Why "wasting" space
Marius NITU, February 09, 2009 - 4:25 am UTC
Hi Tom,
Could you please explain in two words why Oracle stores the length of the CHAR type as it does for VARCHAR. Is it for historical reasons ? It seems that is not the case with other databases and there are some people who thinks that¿s a waste because the length it¿s always known. Personnaly I don¿t think that it really matters.
February 09, 2009 - 7:15 pm UTC
because we always have, a char is just a varchar2 with a leading byte indicator.
And you know what, if the biggest worry in the year 2009 that "others" have is that there is a 1 byte (for 255 and less) and 2 bytes (for 256 and more, but at that point, you are talking about 2 bytes VERSUS a fixed allocation of more than 256) length field - I'd say "they are penny wise and pound foolish"....
There are slightly larger fish to fry, don't you think so?
Marius NITU, February 10, 2009 - 4:48 am UTC
Thanks Tom,
I'm totally agreeing with you.
Just out of curiousity
Saurabh, May 05, 2009 - 6:35 pm UTC
The difference between varchar2 and char is varchar2 supports variable length of characters. So my question is doesn't oracle to has to do something at the back end to make this possible which will affect the performance? I use varchar2 most of the places but few places are there where char seems to be perfectly suitable like states in US(it can not be greater that or less than 2 character)or social security number or telephone number.
May 11, 2009 - 8:58 am UTC
A char in Oracle is simply a varchar2 that has a fixed length - it is blank padded to the maximum length.
Under the covers - they are otherwise *IDENTICAL* - in storage, in retrieval, in everything.
The only difference between:
varchar2(10)
char(10)
is that the char(10) will ALWAYS be either NULL or 10 bytes in length - with blanks provided by us at the end of the string if you do not supply them.
Oh, wait, that is precisely what I originally wrote :(
I would suggest forgetting the existence of the CHAR type - it can only cause confusion and will lead developers to always add "rtrim(column)" in their predicates - leading to poor performance....
srp, May 12, 2009 - 6:10 pm UTC
Thanks for replying. But as far as I know another difference between char and varchar2 is varchar2 will not occupy space for null values. i.e. when we use varchar2(10) and assign it value suppose
state varchar2(10) := 'WI' it will take the space in memory for two characters only where char will take space for whole 10 characters. Now apparently this is advantage of varchar2. But my thinking is it will take more time to store for varchar2(may be important for hard real-time systems). As far as I am concerned I use char where the string value is neither greater than nor less than specified value(like state in USA) so developers don't have to use rtrim.
May 13, 2009 - 11:14 am UTC
... But as far as I know another difference between char and
varchar2 is varchar2 will not occupy space for null values. ...
that is not accurate.
You can say this: ANY column that is NULL and at the end of a row such that no other column after it is NOT NULL occupies 0 bytes. ANY column that is NULL and has a column after it that is NOT NULL will occupy space.
varchar2 and char are the same with regards to NULL.
do not use char, period. the space for a char is not allocated until referenced either.
varchar2(4001) in PL SQL
Atul Gupta, May 20, 2009 - 9:53 am UTC
Hello Tom,
Pls let me know if its more efficient and memory friendly to use varchar2(4001) in PL SQL rather than varchar2(3999) in PL SQL as oracle takes less memory and more optimized to handle varchar2(4001) in PL SQL ?
Since internally if variable declared as varchar2(4000) oracle considers it as sql datatype and varchar2(4001) as pl sql and has special scheme which ensures that memory utilization is less ?
I just heard this and not sure if the above is true ..... let me know if there is any truth in above statements ?
br
atul
May 23, 2009 - 1:04 pm UTC
you were mislead.
use the right length in your code - period
A reader, May 23, 2009 - 10:10 pm UTC
CHAR Vs VARCHAR2
Som Bahadur Limbu, March 23, 2010 - 10:55 am UTC
Hi Tom,
/*****************************************************************************************/
atul - "Pls let me know if its more efficient and memory friendly to use varchar2(4001) in PL
SQL rather than varchar2(3999) in PL SQL as oracle takes less memory and more optimized to handle
varchar2(4001) in PL SQL ? "
Followup May 23, 2009 - 1pm Central time zone:
"you were mislead.
use the right length in your code - period "
/*****************************************************************************************/
The actual value for Atul's question should be varchar2(2000).
What do you have to comment on the below extract from the oracle documentation.
1.
[PL/SQL
User’s Guide and Reference
Release 2 (9.2)
March 2002
Part No. A96624-01]
[Page No. PL/SQL Datatypes 3-9, PL/SQL Datatypes 3-10]
"
Small VARCHAR2 variables are optimized for performance, and larger ones are
optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2
that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to
Predefined Datatypes hold the actual value.
For a VARCHAR2 variable that is shorter than 2000 bytes,
PL/SQL preallocates the full declared length of the variable. For example, if you
assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a
VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter
takes up 1999 bytes."
2.
Oracle® Database
PL/SQL User's Guide and Reference
10g Release 2 (10.2)
B14261-01
June 2005
[Page No. - 3-8 Oracle Database PL/SQL User’s Guide and Reference]
"
Small VARCHAR2 variables are optimized for performance, and larger ones are
optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2
that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to
hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes,
PL/SQL preallocates the full declared length of the variable. For example, if you
assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a
VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes
up 1999 bytes."
March 23, 2010 - 12:54 pm UTC
I don't care what the length is, they were mislead.
You use the right size, not the size of some performance trick, you use the RIGHT SIZE.
that is all, and that is what I mean by mislead, this is not a performance discussion - there is only ONE size to use for your variables...
The right size.
A reader, May 08, 2010 - 9:47 am UTC
Difference between VARCHAR and VARCHAR2?
May 10, 2010 - 6:15 pm UTC
there isn't any as of 11g Release 2 and before.
Thank you!
Olav, October 12, 2010 - 1:38 am UTC
I have been using the Char, Number, etc. for a long time now and on some old computer systems I have been having some issues were I had to use trim(), lpad(), etc.
Mostly this was due to the original design.
Now, after beeing enlightened, as far as I understand - I should use only varchar2 and nvarchar2 for oracle.
This simplifies a lot too for me, as I mainly work on converting old computer systems to oracle and then afterwards make apex pages for them.
Anyhow, thanks :-) I've read this entire thread and I have now seen the light! (In an agnostic way!).
compare char(n) and char(m)
asktom fan, May 10, 2012 - 2:55 pm UTC
Hi Tom,
In your followup dated November 15, 2006 - 6am Central time zone, you said:
<quote>
if you just use varchar2 everywhere, no problem
My advice is, has been, will be very loudly stated as:
IGNORE THE EXISTENCE OF CHAR.
period. If you never use char, you never compare char to varchar2, problem solved.
And if you use char and compare a char(n) to a char(m) they will never compare either.
Just say NO TO CHAR.
</quote>
It appears that the statement you made about comparing char(n) to char(m) is no longer true in Oracle 11.2.0.3. What was the datbase version when you made that statement?
Please see the following:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t (x char(10), y char(15), z varchar(15));
Table created.
SQL> insert into t (x, y, z) values ('hello', 'hello', 'hello');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t where x = y;
X Y Z
---------- --------------- ---------------
hello hello hello
SQL> select * from t where y = z;
no rows selected
Just to be clear, I am not a fan of CHAR either :)
May 10, 2012 - 3:35 pm UTC
I misspoke with the char(n) to char(m), we will cast to the "higher" type in that case.
thanks!
compare char(n) with char(m)
asktom fan, May 10, 2012 - 4:09 pm UTC
Tom, thanks for your quick response.
Can you please clarify what you mean by "we will cast to the "higher" type in that case"?
May 11, 2012 - 1:23 am UTC
the char(n) and char(m) are "normalized" to be char( greatest(n,m) ) and then compared.
char(n), varchar2(n) and literals in the where condition
Prajjwal Mallik, August 23, 2012 - 5:10 am UTC
Hi Tom,
My question is, if the following 2 queries returns the same row, why does "select * from my_tab where aa=bb" not return anything?
select * from my_tab where aa='pqr'
select * from my_tab where bb='pqr';
Regards,
Prajjwal
----------------------- Please refer to the following -----------------------------
SQL> create table my_tab(aa char(5), bb varchar2(5));
Table created.
SQL> insert into my_tab values ('pqr', 'pqr');
1 row created.
SQL> select * from my_tab where aa='pqr'; --no trailing space in the search condition
AA BB
----- -----
pqr pqr
SQL> select * from my_tab where aa='pqr '; --one trailing space in the search condition
AA BB
----- -----
pqr pqr
SQL> select * from my_tab where aa='pqr '; --two trailing spaces in the search condition
AA BB
----- -----
pqr pqr
SQL> select * from my_tab where aa='pqr '; --three trailing spaces in the search condition, length('pqr ')=6 which is greater than 5
AA BB
----- -----
pqr pqr
So, I believe, the search condition involving char datatype does not even care how many trailing spaces are there after 'pqr'.
SQL> select * from my_tab where bb='pqr';
AA BB
----- -----
pqr pqr
SQL> select * from my_tab where aa=bb;
no rows selected
August 29, 2012 - 11:38 am UTC
that is the way char's (stupidly) work - by (committee) design.
when you compare a char(n) to a char(m) - you take the greater of N and M and cast the other one to that size.
So, when you compare a char(5) (your column) to a chr(3) 'pqr', the literal pqr is cast as a char(5) which blank pads it.
When you compare a varchar2(5) to a varchar2(3) - no such casting happens.
never use char, just don't do it.
adderek, January 22, 2013 - 6:46 am UTC
CHAR(1) vs VARCHAR2(1) CONSTANT PL/SQL variable
Shimmy, October 17, 2013 - 2:54 pm UTC
I define a contant as :
DELIMITER CONSTANT CHAR(1) := ';';
Is it better to define it as:
DELIMITER CONSTANT VARCHAR2(1) := ';';
Does VARCHAR2(2) take more memory space?
Is it faster to process CHAR than VARCHAR2 in this case?
November 01, 2013 - 8:13 pm UTC
a char(1) and a varchar2(1) are 100% identical.
they both have a leading byte to tell us their length.
that are the same.
I say to never never never, never ever, use a char type - not even a char(1). A char is just a varchar that takes the maximum width and is blank padded. It is a waste.
char or varchar2 or varchar
Ankush, July 10, 2014 - 6:19 am UTC
we use char when we have the exact length of the variable..in this situation char work more faster than using the varchar.
and there is no such difference between varchar and varchar2.the only difference is the lenght.both are same otherwise...
Adrian Shum, December 05, 2014 - 3:23 am UTC
Ankush: I don't think you have actually read the answer: Using CHAR is
NOT faster than VARCHAR2. At least not in Oracle.
The only case that CHAR outperforms VARCHAR2 is when you are updating the value from a shorter string to a longer one, which will trigger some kind of internal resizing for VARCHAR2 column. However, variable-length data is never be a good candidate to use CHAR
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1542606219593 Checkout the performance test and my update on it
WoundedEgo, June 22, 2015 - 5:25 pm UTC
Significant overlooked difference!:
"...Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes..."
http://docs.oracle.com/cd/A97630_01/appdev.920/a96624/03_types.htm
CHAR remains useful
Peter, December 31, 2015 - 2:48 am UTC
You said...
If the field is in fact ALWAYS 10 bytes long, using a CHAR will not hurt -- HOWEVER, it
will not help either.
The only time I personally use a CHAR type is for CHAR(1). And that is only because its
faster to type char(1) then varchar2(1) -- it offers no advantages.
I recall in another post you advocated using data types as constrained as possible. Granted, CHAR isn't much of a constraint. But when I see CHAR(9) vs VARCHAR2(9), they have the expected different meanings and the meanings are useful to communicate to humans. But where we still store things like an SSN, CHAR(9) is more meaningful than VARCHAR2(9). At this point in time, SSNs will always have 9 character (digits). The column name isn't always going to make this obvious.
January 01, 2016 - 4:41 am UTC
I see your point, but it would still would not stop me from storing a 6 char SSN in there, unless I add some constraints about the *content* of the data. And if I'm doing that, then potentially we're back to varchar2 being 'just as good'
QUESTION
Amar, July 21, 2016 - 10:43 am UTC
Sir,
will you please give me the difference between
11g char/varchar/varchar2 datatypes
and
12g char/varchar/varchar2 datatypes
thanks ..
July 21, 2016 - 11:08 am UTC
QUESTION
Amarnath Sharma, July 21, 2016 - 12:24 pm UTC
Sir,
when I use 11g char/varchar/varchar2 it accepts number in it
but when I use 12c char/varchar/varchar2 it doesn't accept number ,why please help me
thank you
July 21, 2016 - 12:41 pm UTC
What do you mean, "it doesn't accept number"?
Show us!
The size clause works the same in 12c as it does in 11g. Though you can go up to 32,767 if you have extended data types enabled. The 11g limit is 4,000.
Ivan, July 18, 2018 - 5:05 pm UTC
Excellent explanation.