The difference and connection of CHAR,VCHAR,VCHAR2 in Oracle

1.char is fixed-length, for example, you define a column type of char (20). So even if you insert "ABCDE" 5 bytes, the database will automatically add 15 bytes (possibly a space) to complement the 20 bytes.

2.vchar

Vchar are long. For example, if you define a column as Vchar (20), you insert "ABCDE" 5 bytes, the database stores only 5 bytes. 20 is the maximum length that the database stores for the column.

3.vchar2

VCHAR2 is an Oracle-defined type, essentially equivalent to Vchar. Only Vchar can store an empty string. And Vchar2 stores null values. Oracle generally recommends using VCHAR2.

4.VCHAR2 (Byte) and Vchar2 (char)

The former has a maximum storage of 20 bytes, the latter storing up to 20 characters. For example, for Chinese, a character may be 2 bytes. So if you want to store content in Chinese, use VCHAR2.

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

The difference and connection of CHAR,VCHAR,VCHAR2 in Oracle