Introduction to bytes occupied by Oracle 10 GB Data Type

This articleArticleThe main purpose is to analyze the storage space used for building the system to avoid the waste of storage device transition and save investment. It also improves the common usage of OracleDataType.

IntroductionThe types are as follows:
· Char
· Varchar
· Varchar2
· Nvarchar
· Nvarchar2
· Number
· Date

The Char length is fixed. For example, if you define char (20), even if you insert ABC, less than 20 bytes, the database will automatically add 17 spaces after ABC, to supplement 20 bytes; char is distinguished between Chinese and English, Chinese occupies two bytes in char, and English occupies one, so char (20) you can only store 20 letters or 10 Chinese characters. Char is suitable for characters with fixed length and generally does not contain Chinese characters. Char is more efficient than varchar2.

Currently, varchar is a synonym for varchar2. The industry standard varchar type can store null strings, but Oracle has developed a data type varchar2, which is not a standard varchar, it changes the varchar column in the database to the attribute that can store null strings to store null values. If you want to achieve backward capacity, we recommend that you use varchar2 instead of varchar.

Varchar and varchar2 are not fixed in length. For example, you have defined varchar (20). When you insert ABC, only three bytes are occupied in the database. Chinese occupies two bytes.

Varchar and varchar2 can store up to 4000 bytes.

Nvarchar and nvarchar2 are not fixed in length. nvarchar does not distinguish Chinese and English. For example, if you define nvarchar (20), you can store 20 English letters, Chinese characters, 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. The difference is that the English letters in nvarchar2 also occupy two bytes. nvarchar/nvarchar2 is suitable for Chinese characters.

A number can contain up to 22 bytes and can contain up to 38 decimal digits.
by default, the number has a precision of 38 BITs. The value range is 1 ~ Between 38. It is actually a variable-length type on the disk, it will occupy 0 ~ 22 bytes of storage space. But how many bytes does a defined number field occupy? The following formula is found on the Internet.
There are formulas for calculation:
Number (p, s) occupied space:
length = floor (p + 1)/2) + 1
Note: if the value is negative, add a byte.
----------------
example: Number) the value of type. The storage space is
select floor (14 + 1)/2) + 1 from dual
result output: 8

The date type occupies 7 bytes in Oracle10g.

When you roughly estimate the space occupied by each record in a table, you can refer to the preceding rules to query data dictionaries.

Select * From user_tab_columns where table_name = '';

In the query results, there is a column named data_length, which records the maximum number of bytes occupied.