Oracle基础类型

char相关类型比较

CHAR, VARCHAR, VARCHAR2 在性能上区别不大

VARCHAR2：节省空间 CHAR：效率上稍好

比如：一个VARCHAR2列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移’(RowMigration)现象，而这造成多余的I/O。所以此时最好使用CHAR。

CHAR适用场景： 固定长度； 短信息； 频繁该改变的列

inner data types

store character (alphanumeric) data in strings, with byte values corresponding to the character encoding scheme

  • CHAR 定长， 默认1byte，范围1 to 2000 bytes （or characters）. 不足，则默认填充空 超过长度，则报错

  • VARCHAR2 可变长 1 to 4000 bytes

  • VARCHAR 语义和VARCHAR2相同，但为了避免可能的行为改变，通常使用VARCHAR2 VARCHAR is an ANSI-standard data type that supports a distinction between NULL and empty strings. 后续版本已经不区分NULL和空字符串的区别了。在Oracle中，null和空字符串是一个意思。常用null来进行判断。

  • NCHAR Unicode datatypes that store Unicode character data(可以存储任何语言) only support AL16UTF16 and UTF8 only use character length semantics max limit to 2000 characters (also limit to 2000 bytes), two constraints

  • NVARCHAR2 character length semantics max limit to 4000 characters(also limit to 4000 bytes)

  • LONG store variable-length character data containing up to 2 gigabytes of information do not use this, use CLOB or NCLOB instead

  • CLOB

  • NCLOB store up to 8 terabytes of character data (CLOB) or national character set data (NCLOB).

  • NUMBER decimal precision 固定和浮点数

  • BINARY_FLOAT

  • BINARY_FLOAT binary precision

  • DATE 内部格式存储 相减结构为：天

  • DATETIME stores the fractional seconds: TIMESTAMP(3) to milliseconds 便于计算时间差 TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE

two semetics

  • Byte semantics: default. The real storage size depend on the encoding schema. Character sequence to bytes sequence.

  • Character sematics: treat strings as a sequence of characters. A character is technically a codepoint of the database character set.

example: in a Unicode database, need to store up five Chinese characters together with five English characters.

VARCHAR2(20 BYTE) and SUBSTRB(, 1, 20) [35 + 15= 15]

VARCHAR2(10 CHAR) and SUBSTR(, 1, 10) [15 + 15 = 10]

reference

oracle data types