Comparison and use of Oracle char and varchar2

Recently, I have dealt with some Oracle character data types. I want to learn from them as follows:

1. First, compare the char and varchar2 types:

Differences:

1. the length of CHAR is fixed, while the length of VARCHAR2 can be changed. For example, the storage string "abc", for CHAR (20 ), it indicates that the characters you store will occupy 20 bytes (including 17 null characters), while the same VARCHAR2 (20) takes up only 3 bytes, and 20 is the maximum value, when the characters you store are less than 20 characters, they are stored according to the actual length.

Example:

SQL> create table testchar (c1 char (20), c2 varchar2 (20 ));

The table has been created.

SQL> insert into testchar values ('dylan', 'dylan ');

One row has been created.

SQL> commit;

Submitted.

SQL> select length (t. c1), length (t. c2) from testchar t;

LENGTH (T. C1) LENGTH (T. C2)
------------------------
20 5

2. CHAR is more efficient than VARCHAR2.

3. Currently, VARCHAR is a synonym for varchar2. The industry standard VARCHAR type can store null strings, but oracle does not, although it reserves the right to do so in the future. Oracle has developed a data type VARCHAR2, which is not a standard VARCHAR. It changes the feature that can store NULL strings in the varchar column of the database to store NULL values. If you want backward compatibility, we recommend that you use VARCHAR2 instead of VARCHAR.

When should CHAR be used and varchar2 be used?

CHAR and VARCHAR2 are a contradictory unity, and they are complementary.

VARCHAR2 saves space than CHAR, and is slightly less efficient than CHAR. That is, to achieve efficiency, a certain amount of space must be sacrificed, this is what we often say in database design, "Change space for efficiency '.

Although VARCHAR2 saves space than CHAR, if a VARCHAR2 column is often modified and the length of the data to be modified is different each time, this will cause Row Migration, this causes redundant I/O, which should be avoided during database design and adjustment. In this case, it would be better to replace VARCHAR2 with CHAR.

Ii. Questions about the storage space occupied by oracle Chinese Characters

Method 1:
The number of bytes of a Chinese character in the Oracle database is related to the character set of the database. The length of UTF8 is 3.

TEST How many bytes a Chinese character occupies in various character sets:

SQL> select lengthb ('hang') from dual;

LENGTHB ('hangzhou ')
-------------
2

Method 2:

View the current character set of the oracle database:

SQL> select * from nls_database_parameters where parameter = 'nls _ CHARACTERSET ';

PARAMETER VALUE
----------------------------------------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK

How many characters is a Chinese character and an English letter? We know that a Chinese character is a dubyte character, but it has several characters related to its database character set. If the database character set uses single-byte US7ASCII, one Chinese character is two characters. If the database character set uses the dual-Byte Character Set ZHS16GBK, one Chinese character is one character. You can use the Substr function of Oracle to prove this.

When US7ASC Ⅱ is used:

SQL> select substr ('nanjing University ', 1, 2) from dual;

SUBS
----
South

When using the ZHS16GBK character set:

SQL> select substr ('nanjing University ', 1, 2) from dual;

SUBS
----
Nanjing

1 2 Next Page