Solved

VARCHAR2 or CHAR, does it matter?

Posted on 2004-03-28
10
19,225 Views
Last Modified: 2012-05-04
I have a VARCHAR2(10) column which will always stores data of length 10 characters.  'Should' I alter this table to make it a CHAR(10), or does it not really matter?  (I know the basic about these data types, but want to know what best-practice advises on the matter)

Q1: Should I modify my column from VARCHAR2(10) to CHAR(10)? (Yes/No-don't bother)
Q2: Will Oracle treat the column differently? (Storage/performance)




0
Comment
Question by:derekpapesch
10 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 175 total points
ID: 10701087
Q1: Should I modify my column from VARCHAR2(10) to CHAR(10)? (Yes/No-don't bother)

IMHO, No -don't bother.

Q2: Will Oracle treat the column differently? (Storage/performance)

No difference as long as you are alway expecting 10 chars long data.

performance will be the same as is the storage.
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 10701119

Q1: Should I modify my column from VARCHAR2(10) to CHAR(10)? (Yes/No-don't bother)
let's
CHAR (size) – A fixed-sized field of characters. The largest this particular datatype can become is 2000 bytes. In other words, it can only hold 2000 characters. If you don’t specify the length of the CHAR datatype, the default size is a single character (i.e. 1 byte).

if you define char(10) and the column value is "ABC", Oracle will fill the column value 10 characters with spaces automatically.

VARCHAR2 (size) – A variable-sized field of characters. The largest this datatype can become is 4000 characters and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.

Oracle will not do the same thing for Varchar2 like for char.

So , in your case, you do not have to change from VARCHAR2(10) to CHAR(10).

Q2: Will Oracle treat the column differently? (Storage/performance)
   It's sure Oracle treat them differently.
  when you define char(10), it's sure oracle will use 10 bytes space even you have less than 10 characters.
   But for Varchar2, when you define varchar2(20) and the value is "ABC", it only use 3bytes not 20 bytes space.
   so for the fixed length string, you can select char data type. for varing length string ,select varchar2 data type.
   Accessing Char data type is faster than access varchar2 data type. but for a regular table and a small column, it hard to say how big the difference is.

 



0
 
LVL 4

Author Comment

by:derekpapesch
ID: 10701220
Seazodiac: No
Danielzt: ?

Danielzt: My question is 'should I', not 'must I'.  I'm not certian what your opinion is on this.  As you state that char IS faster, I assume your answer is Yes (I should change it).

Water's still a bit muddy.  Any further clarification, references or opinions?


0
 
LVL 2

Expert Comment

by:n4nazim
ID: 10701485
Hi,

Q1: Should I modify my column from VARCHAR2(10) to CHAR(10)?
No-don't bother ( Why ??? - See answer for Q2 )

Q2: Will Oracle treat the column differently? (Storage/performance)

When u define a column of type CHAR(15) and store value "ABCDE", Oracle will automatically fill the rest with spaces.

For example 15 - Len("ABCDE") : that it will fill in 10 spaces. So as u see this takes a bit more hard-disk space.

Whereas VARCHAR2 will NOT FILL THE REST WITH SPACES as a result will save hard-disk space.

THIS IS HOW ORACLE TREATS CHAR & VARCHAR

Hope yr views are cleared ...
Rgds,
NHM

0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 125 total points
ID: 10701853
You always use 10 characters, so you do not use the VARCHAR2 main property, i.e. variable length.

Pro and contra:
1. Move to CHAR.
This type do not use prefixes for the length (VARCHAR2 uses 1 byte additionally for lengts under 250 bytes and 2 bytes for big lengts). So you will conserve 1-2 bytes pro column.

But CHAR is an old type used in the past. Orackle doesn't recommend to use it in future developments.

2. Move to VARCHAR2

It is flexible in regard to the length (up to 4000).
It is flexible in regard to the multibyte character sets:
name VARCHAR2 (50 char); -- in 9i and higher
means 50 characters, not 50 bytes. So every character could be 4 bytes or the column could be up to 200 bytes.

So I will recommend you to move to VARCHAR2 in order to avoid FUTURE troubles.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Assisted Solution

by:andrewst
andrewst earned 25 total points
ID: 10702611
Actually, the choice even even clearer because CHAR also uses 1-2 bytes to store the length, so there is no space conservation in using CHAR over VARCHAR2 ever!

Also, Danielzt, where did you get the information that CHAR is faster to access than VARCHAR2?  I have not seen that claimed before.

IMHO, it would be best to simply forget that CHAR exists and always use VARCHAR2.
0
 
LVL 10

Expert Comment

by:SDutta
ID: 10704996
As far as Derek's question there is no difference in storage/performance for this specific case.

As a general case, if you store less than the maximum specifed X for VARCHAR2(X) it will lead to better performance than CHAR(X) due to the records being more densely packed. The reason as many already described is because CHAR fills in spaces if you dont use the full size as defined.
0
 
LVL 8

Assisted Solution

by:Danielzt
Danielzt earned 100 total points
ID: 10705230
Hi derekpapesch&andrewst

if you are working in a pure oracle environment. Just use varchar2 instead of char.
..........
http://www.unix.org.ua/orelly/oracle/prog2/ch04_02.htm
You will rarely need or want to use the CHAR datatype in Oracle-based applications. In fact, I recommend that you never use CHAR unless there is a specific requirement for fixed-length strings or unless you are working with data sources like DB2. Character data in DB2 is almost always stored in fixed-length format due to performance problems associated with variable-length storage. So, if you build applications that are based on DB2, you may have to take fixed-length data into account in your SQL statements and in your procedural code. You may, for example, need to use RTRIM to remove trailing spaces from (or RPAD to pad spaces onto) many of your variables in order to allow string comparisons to function properly.
........

Some people experienced performance problem when they use oracle with DB2 and Sybase databases.
0
 
LVL 3

Assisted Solution

by:rajnadimpalli
rajnadimpalli earned 75 total points
ID: 10708361
Interesting "old" topic...here's my take...

Your case "I ALWAYS STORES DATA of LENGHT 10 Characters"  means Fixed length column.

Q1: Should I modify my column from VARCHAR2(10) to CHAR(10)? (Yes/No-don't bother) :     Ans : don't bother

Q2 : Will Oracle treat the column differently? (Storage/performance) :

a) Storage : There is no difference in your case .you actually input 10 characters..so char(10) will use 10 bytes , varchar2(10) will use 10 bytes...for each and every row...no difference...see test case...for details...

b) Performance : well instead of saying yes/no...I will do a quick test to see if there are any real differences.Its seem there is No different. If any.. you may not able to notice measurable performance differences. See below for simple test case situation..

So bottom-line : "don't bother" in your case...

But ,there are always special situation..like ..designing a Billion row table in VLDB data warehouse where even when you are sure that you are going to input 10chars ..I say use varchar2(10) instead of char(10)..because most Data Warehouse..I worked ..observed that..business requirements are so dynamic..and data sources are so different..you always..can not able say..that "I ALWAYS STORES DATA of LENGTH 10 Characters"  because business requirement may change in future.If you want to store a variable length (increase/decrease length) ..in this field in future ...then its costs you..big bucks..

1. Some use "No-CHAR policy" : means use varchar2 always...for performance reasons.But I don't see that.

2. char and varchar2 datatype will be supported by Oracle in all future versions .see PL/SQL Application developer guide and Metalink..and 10g..so don't worry. "Char" datatype will be there..its ANSI- SQL99 standard...

Test case :

14:31:08 SQL> create table DEMO
14:31:11   2  (
14:31:13   3  COL_CHAR10 CHAR(10),
14:31:16   4  COL_VARCHAR10 VARCHAR2(10)
14:31:17   5  )
14:31:20   6  /

Table created.

14:31:30 SQL>  desc DEMO
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 COL_CHAR10                             CHAR(10)
 COL_VARCHAR10                          VARCHAR2(10)

14:31:34 SQL>

14:32:14 SQL> insert into DEMO
14:32:17   2  values
14:32:20   3  ('1234567890','1234567890')
14:32:25   4  /

1 row created.

14:33:00 SQL> insert into DEMO
14:33:04   2  values
14:33:05   3  ('ABCDEFGHIJ','ABCDEFGHIJ')
14:33:10   4  /

1 row created.

14:33:11 SQL> commit;

Commit complete.

14:33:14 SQL> select * from DEMO;

COL_CHAR10 COL_VARCHA
---------- ----------
1234567890 1234567890
ABCDEFGHIJ ABCDEFGHIJ

14:33:19 SQL>
15:12:39 SQL> begin
15:12:42   2  for i in 1..20 loop
15:12:44   3   insert into DEMO select * from DEMO;
15:12:50   4  end loop;
15:12:53   5  end;
15:12:54   6  /

PL/SQL procedure successfully completed.

15:13:10 SQL> commit;

Commit complete.

15:13:14 SQL> select count(*) from  DEMO;

  COUNT(*)
----------
   2097152

15:13:24 SQL>

15:18:24 SQL> create index indx_char on DEMO(COL_CHAR10);

Index created.

15:19:27 SQL> create index indx_varchar on DEMO(COL_VARCHAR10);

Index created.

15:23:58 SQL> exec dbms_stats.gather_table_stats(user,'DEMO');

PL/SQL procedure successfully completed.

15:24:10 SQL>
15:27:52 SQL> ;
  1  select SEGMENT_NAME,bytes from user_segments
  2* where SEGMENT_NAME in ('DEMO','INDX_CHAR','INDX_VARCHAR')
15:27:57 SQL> /


SEGMENT_NAME              BYTES
-------------------- ----------
DEMO                   65011712
INDX_CHAR              54525952 <-- index storage ..
INDX_VARCHAR           54525952

15:28:15 SQL>

15:30:20 SQL> select count(*) from demo
15:30:26   2  where COL_CHAR10='1234567890'
15:30:43   3  /


  COUNT(*)
----------
   1048576


Execution Plan
----------------------------------------------------------
   0
SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
   1    0
  SORT (AGGREGATE)
   2    1
    INDEX (FAST FULL SCAN) OF 'INDX_CHAR' (NON-UNIQUE) (Cost=4 Card=1048576 Bytes=11534336)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6462  consistent gets <--- *
       6453  physical reads
          0  redo size
        382  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

15:30:47 SQL> select count(*) from demo
15:31:02   2  where COL_VARCHAR10='1234567890'
15:31:27   3  /

  COUNT(*)
----------
   1048576

Execution Plan
----------------------------------------------------------
   0
SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
   1    0
  SORT (AGGREGATE)
   2    1
    INDEX (FAST FULL SCAN) OF 'INDX_VARCHAR' (NON-UNIQUE) (Cost=4 Card=1048576 Bytes=11534336)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6462  consistent gets <--- *
       6453  physical reads
          0  redo size
        382  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

15:31:29 SQL>


-Raj
0
 
LVL 4

Author Comment

by:derekpapesch
ID: 10709475
Thanks for your comments.

I appreciated Seazodiac's first response, schwertner's comments on multi-byte character sets (I use them a lot), andrewst's simplification (forget CHAR), Danielzt's reference to O'Reilly, and rajnadimpalli's performance test.

Brilliant answer!

Derek
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now