Today, the oracle in learning the basic data types, Oracle data types supported can be divided into three basic categories: character data types, numeric data types and data types, said other data, its data types were compared and analyzed.
char length is fixed, for example, you define the char (20), even if you insert your abc, less than twenty bytes, the database will automatically add the abc 17 spaces back to complement the twenty bytes;
char is a distinction in English, Chinese accounted for two bytes in the char, while English accounted for one, so char (20) you can only save 20 characters or 10 characters.
char length of relatively fixed for the general case of non-Chinese
varchar is not fixed length, for example, you define the varchar (20), when you insert abc, then in the database only 3 bytes.
varchar same distinction in English, this is the same char.
varchar2 is basically equivalent to varchar, it is the oracle himself a non-industry standard definition of varchar, difference is, varchar2 varchar with null instead of empty string
varchar/varchar2 for a fixed length, the general situation of non-Chinese
nvarchar and nvarchar2 is a fixed length
nvarchar does not distinguish between Chinese and English, for example: you define nvarchar (20), you can deposit 20 letters / characters or combination of Chinese and English, this definition is 20 characters, not bytes
nvarchar2 basically equivalent to nvarchar, difference is in memory of the letters nvarchar2 also accounted for two-byte
nvarchar/nvarchar2 for storage of Chinese
Fixed length, non-Unicode character data, length n bytes. n ranges from 1 to 8,000, the storage size is n bytes.
varchar [(n | max)]
Variable-length, non-Unicode character data. n ranges from 1 to 8,000. max indicates the maximum storage size is 2 ^ 31-1 bytes. Storage size is the actual length of the input data plus 2 bytes used to reflect the length of the stored data. The length of the input data can be 0 characters.
* If the size of the column the same data item, use char.
* If the column data entries vary considerably in size, use varchar.
* If the size of the column data entries vary considerably, and may exceed the size of 8,000 bytes, use varchar (max).
If you are not in the data definition or variable declaration statement, char or varchar data type specified n, the default length is 1. If you use the CAST and CONVERT functions char or varchar data type is not specified n, the default length is 30.
When the implementation of CREATE TABLE or ALTER TABLE, if the SET ANSI_PADDING is OFF, the char is defined as NULL columns as varchar treatment.
In addition to help understand, for reference only: Transfer from http://www.51testing.com/?uid-258885-action-viewspace-itemid-141197
Can also refer to learning http://ce.sysu.edu.cn/garden/dispbbs.asp?boardid=26&ID=8774&replyID=18180&skin=1
1.NULL value (empty value).
a. char NULL column takes the value of storage space.
b. varcahr NULL column values do not take up storage space.
c. Insert the same number of NULL values, varchar columns into char columns efficiency was significantly higher.
2. Insert Data
Insert columns of data regardless of whether the involved index, char efficiency were significantly lower than varchar.
3. Update Data
If the update is not indexed on the columns, the lower the efficiency of char varchar, little difference; indexed words, efficiency is higher.
4. Modify the structure
a. either add or delete columns of type char or varchar, operation can be completed faster and there is no difference in efficiency.
b. For the purposes of increasing the width of the column, char and varchar are very obvious differences in the efficiency of, modify varcahr basically do not take time out, and modify the char column takes a long time.
5. Data Retrieval
Whether through the index, varchar data type char to retrieve a little better than the scan.
Choose the recommendations of char or varchar selected
1. Appropriate to the situation in the char:
a. column lines are basically the same data length, length variation does not exceed 50 bytes;
b. data changes frequently, less demand for data retrieval.
c. The length of the column will not change, modify the width of char type column the cost is relatively large.
d. column does not appear a large number of NULL values.
e. out too much on the need to create the index, the index too much data changes on the char column greater impact.
2. Suitable for the case of varchar;
a. column lines are great differences in the length of the data.
b. update the data in the column is very small, but check frequently.
c. often does not list data for the NULL value or empty value
n-character fixed-length Unicode character data. n value must be between 1 and 4,000 (inclusive). Twice the storage size is n bytes.
nvarchar [(n | max)]
Variable-length Unicode character data. n values between 1 and 4,000 (inclusive). max indicates the maximum storage size is 2 ^ 31-1 bytes. Storage size is twice the number of characters entered + 2 bytes. The length of the input data can be 0 characters.
If there is no definition or variable declaration in the data specified in the statement n, the default length is 1. If you do not use the CAST function to specify n, the default length is 30.
If the size of the column may be the same data item, use the nchar.
If the column data entries vary widely in size, use nvarchar.
sysname is a system of user-defined data types, in addition to non-null values, the functional and nvarchar (128) the same. sysname used to reference the database object names.
Nchar or nvarchar for the use of the object is assigned the default collation of the database, but can use the COLLATE clause to assign a specific collation.
SET ANSI_PADDING ON for nchar and never nvarchar. SET ANSI_PADDING OFF does not apply to nchar or nvarchar data types.
In Oracle of CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2 the difference between the five types
1.CHAR (size) and VARCHAR (size) of the difference between
CHAR is fixed-length field, the maximum length of 2K bytes;
VARCHAR is variable length field, the maximum length of 4K bytes;
2.CHAR (size) and NCHAR (size) of the difference between
CHAR account if stored 1 byte alphanumeric, character encoding GBK store store 2 bytes, stored UTF-8 encoded character takes 3 bytes;
NCHAR character set to define the selected stored number of bytes occupied by the characters, usually stored as a 2-byte characters (regardless of character or characters)
3.VARCHAR (size) and VARCHAR2 (size) of the difference in the present version, there is no difference between the two; maximum length of 4K bytes; recommended VARCHAR2;
4.VARCHAR2 (size) and NVARCHAR2 (size) of the difference between the maximum length of 4K bytes, the difference with the difference between CHAR and NCHAR; (if the database character set length is 2, then NVARCHAR2 up to 2K)
5. The common characteristics of the time when performing insert, insert a value of'', then into a null, the insert ... values ('') <=> insert ... values (null)
Search criteria to be used where xx is null
6. Examples of such a gender field, which kept "men and women," one of the values, two common options
CHAR (2) and NCHAR (1)