Oracle field type and storage (i)

Source: Internet
Author: User

2000 byte in Oracle, not 2000 characters, 1 characters in Oracle may be 1 byte to 4 byte, depending on the setting of the database character set.

For the GBK character set, 128 characters in the ASCII code are stored using 1 bytes, the other characters are stored in double-byte storage, 128 characters in the ASCII code are stored using 1 bytes for the UTF8 character set, and the other is stored in two or three bytes. Four bytes are stored with very few characters;

The database has a parameter nls_length_semantics, which has two values of char and byte. The length of the character type is calculated as char, rather than in byte, which is useful when using the variable-length character set (Al32utf8), because the number of bytes that a character occupies is variable, which gives us an accurate estimate of the length of the field (byte).

1, CHAR.

Char is convenient to store the fixed-length data, the index on the Char field is more efficient, such as the definition of char (10), then regardless of whether you store data reached 10 bytes, take up 10 bytes of space, insufficient to automatically fill with spaces.

2. VARCHAR

VARCHAR stores variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used. From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.

3. TEXT

Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters.

4, NCHAR, NVARCHAR, NTEXT

These three kinds of names from the first three more than the previous "N". It represents a character stored in a Unicode data type. We know that characters, the English character only need a byte storage is enough, but the number of Chinese characters, need two bytes of storage, English and Chinese characters at the same time prone to confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, That is, the English character is also represented in two bytes.

5. Character set and national character set

1> Character Set
(1) used to store char, VARCHAR2, CLOB, long and other types of data
(2) to indicate such as table name, column name, and PL/SQL variable, etc.
(3) used to store SQL and PL + + program units, etc.

2> National Character Set

(1) to store nchar, NVARCHAR2, NCLOB and other types of data
(2) The national character set is essentially the additional character set chosen for Oracle, primarily to enhance the character processing capabilities of Oracle, since the nchar data type provides support for the use of fixed-length multibyte encoding in Asia, and the database character set is not. The national character set is redefined in oracle9i and can only be selected in Af16utf16 and UTF8 in Unicode encoding, and the default value is Af16utf16

Field type Chinese description Restriction conditions Other instructions
CHAR Fixed-length strings Maximum length 2000bytes
VARCHAR2 Variable-length strings Maximum length 4000bytes Maximum length of index can be made 749
NCHAR Fixed-length strings based on character set Maximum length 2000bytes
NVARCHAR2 Variable-length strings based on character set Maximum length 4000bytes
DATE Date (day-month-year) Dd-mm-yy (HH-MI-SS) After rigorous testing, no problem of thousand worms
LONG Super Long string Maximum length 2G (231-1) Enough to store a tome
RAW Fixed-length binary data Maximum length 2000bytes can store multimedia image sound, etc.
LONG RAW Variable-length binary data Maximum length 2G Ditto
Blob Binary data Maximum length 4G
Clob Character data Maximum length 4G
NCLOB Character data based on the character set Maximum length 4G
BFILE Binary data stored outside the database Maximum length 4G
ROWID Unique line number recorded in the datasheet 10bytes . ****.**** format, * is 0 or 1
Nrowid Unique line number recorded in a Binary data table Maximum length 4000bytes
Number (P S) Number Type P is the integer digit, S is the decimal digit
DECIMAL (P S) Number Type P is the integer digit, S is the decimal digit
INTEGER Integer type A small integer
FLOAT Floating-point type Number (38), double precision
REAL Real type Number (63), higher accuracy

Oracle field type and storage (i)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.