CHAR vs VARCHAR Difference, Advantage in Performance Tuning

advertisements

_____________________________________________________________________________________________________________________

Please see some examples on char and varchar datatype.

create table example_Char (vchar_col1 varchar(10), char_col2 char(10))

SQL> /

Table created.

SQL>

insert into example_char values ('TEMP','TEMP')

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select length(vchar_col1), length(char_col2) from example_Char;

LENGTH(VCHAR_COL1) LENGTH(CHAR_COL2)

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

                 4                10

select dump(vchar_col1) vchardataASCII,dump(char_col2) chardataASCII

from example_Char;

SQL> /

VCHARDATAASCII                 CHARDATAASCII

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

Typ=1 Len=4: 84,69,77,80       Typ=96 Len=10: 84,69,77,80,32,32,32,32,32,32

Char variables stores the data in fixed length including the trailing space where as varchar variables store only the data. Varchar is used to store variable character length character strings. String length will be stored with string value on the disk. For Char variables are in fixed length as it will be right padded with blank space before storing to the disk. In the above example you can 32, 32,… etc values for CHAR datatype. This will waste lot of disk space.

Now let us discuss about the performance tuning and advantages.

VARCHAR2 and CHAR behave differently in some comparison operations and it is generally regarded as unwise to mix them arbitrarily within a data model. Because VARCHAR2 columns tent to require less storage, rows that are comprised of VARCHAR2 tent to be shorter than rows based on CHAR columns; and hence tables that are constructed with VARCHAR2 will also be smaller. Smaller tables will be less expensive to scan, and indexes on VARCHAR2 will be smaller and somewhat more efficient.

CHAR data types do have at least one performance advantage over VARCHAR2. When a CHAR column is updated so that it becomes longer, free space within the row for the update is guaranteed. On the other hand, if a VARCHAR2 column is updated, it is possible that there will not be sufficient free space to support the update. This may result in the row being chained to separate block, degrading index lookups on this row.

Note: Use VARCHAR2 in preference to CHAR in order to reduce row length and optimize table scans unless the risk of row chaining is excessive.

_____________________________________________________________________________________________________________________

Website Stats