The difference and use of Oracle Char,varchar,varchar2,nvarchar type (reprint)

a varchar,varchar2,nvarchar,nvarchar2
All four types belong to the variable-length character type, and the difference between varchar and VARCHAR2 is two bytes for all characters in the latter, and the former only has two bytes for kanji and full-width characters, all of which are non-Unicode character data and can be defined with a maximum length of 4000 bytes. The difference between nvarchar and nvarchar2 is the same as above, which differs from the type defined by the Unicode standard (the length defines the number of characters, not the number of bytes), and is typically used to support the definition of a similar system in multiple languages, with a maximum length of 4000 characters that can be defined, 8000 bytes.

1.char

The length of char is fixed, for example, you define char (20), even if you insert ABC, less than 20 bytes, the database will automatically add 17 spaces after ABC to complement 20 bytes;
Char is a distinction between English and Chinese, which occupies two bytes in char, and English occupies one, so char (20) You can only save 20 letters or 10 characters.
char is suitable for fixed length, generally does not contain Chinese situation

2.varchar/varchar2

varchar is not fixed in length, for example, you define varchar (20), and when you insert ABC, it only occupies 3 bytes in the database.
varchar also distinguishes between English and Chinese, and this is the same as Char.
VARCHAR2 is basically the same as varchar, which is a non-industry standard varchar defined by Oracle itself, except that VARCHAR2 uses NULL instead of the empty string of varchar
VARCHAR/VARCHAR2 is suitable for non-fixed length, generally does not contain Chinese situation

3.nvarchar/nvarchar2

nvarchar and nvarchar2 are not fixed in length, and do not distinguish between Chinese and English, whose length defines the number of characters
nvarchar does not distinguish between Chinese and English, such as: you define nvarchar (20), you can deposit 20 English letters/kanji or Chinese and English combinations, this 20 defines the number of characters rather than the number of bytes
Nvarchar2 is basically the same as nvarchar, except that the English alphabet, which is stored in nvarchar2, also accounts for two bytes.
nvarchar/nvarchar2 for storing Chinese

1.NULL value (null value).

A. The null value of the char column occupies storage space. Char type can define an empty string

B. The null value of the Varcahr column does not occupy storage space. The empty string defined by the varchar type is treated as null

C. Insert the same number of null values, and the varchar column is significantly more efficient than the Char column.

2. Inserting data

Char is significantly less efficient than varchar, regardless of whether the columns involved in the inserted data are indexed.

3. Updating data

If the updated column is not indexed, the efficiency of char is less than varchar, the difference is not big;

4. Modify the structure

A. Regardless of the type of column that is added or deleted is char or varchar, the operation can be done faster and with no difference in efficiency.
B. For increasing column widths, Char has a very significant efficiency difference from varchar, and modifying the Varcahr column does not take much time, and modifying the Char column takes a long time.

5. Data retrieval
The data retrieval of the varchar type is slightly better than the scan of char, regardless of whether it is indexed.
To select char or to choose a varchar suggestion

1. Conditions suitable for char:

A. The length of each row in the column is basically the same, and the length varies by no more than 50 bytes;
B. Data changes are frequent and there is less demand for data retrieval.
C. The length of the column does not change, and the cost of modifying the width of the char type column is relatively high.
D. A large number of null values do not appear in the column.
E. There is no need to create too many indexes on the column, and too many indexes have a large impact on data changes in the Char column.

2. Conditions suitable for varchar;

A. The length of the rows in the column varies significantly.
B. There are very few updates to the data in the column, but the queries are very frequent.
C. Columns often have no data, null values or null values

Comparison of occupancy lengths:

the difference between 1.CHAR (size) and varchar (size)
Char is a fixed-length field with a maximum length of 2K bytes;
varchar is a variable-length field with a maximum length of 4K bytes;

difference between 2.CHAR (size) and nchar (size)
Char If storing alphanumeric accounts for 1 bytes, storing GBK encoded characters in 2 bytes, storing UTF-8 encoded characters occupies 3 bytes;
nchar defines the number of bytes used to hold characters based on the selected character set, typically holding one character for 2 bytes (regardless of character or kanji)

difference between 3.VARCHAR (size) and VARCHAR2 (size)
The latter all the characters accounted for two bytes, the former only for Chinese characters and full-width two bytes, if the characters are stored in Chinese and English data, then the number of characters stored, varchar more than VARCHAR2, the maximum length is 4K bytes; recommended use VARCHAR2;

differences between 4.VARCHAR2 (size) and NVARCHAR2 (size)
The latter takes all characters to two bytes, which account for only two bytes of kanji and full-width characters; (If the database character set length is 2, the NVARCHAR2 is 2K maximum)

Because the definition length is the number of characters, nvarchar and nvarchar2 define the same length, and if the data is both Chinese and English and numbers, the nvarchar occupies less storage space than nvarchar2

Original address: http://www.cnblogs.com/ItisFriday/archive/2010/09/24/1832971.html

The difference and use of Oracle Char,varchar,varchar2,nvarchar type (reprint)