Analysis on the storage format of Oracle basic data types (II) -- numerical type

This article mainly describes the data of the number type and how to store it in the database.

Composition of the 1 Number Type

The number type of Oracle consists of up to three parts, which are "highest bit", "Data Part", and "symbol bit ".

The negative number contains the symbol bit. The positive number does not include the symbol bit.

In addition, the value 0 is special. It only contains the highest bit of a value to indicate the bit 80. There is no data or sign bit.

1.1 highest bit

The highest bit of a positive number indicates that the bit is greater than 80, and the highest bit of a negative number indicates that the bit is smaller than 80.

If the highest bit of a positive number is a single bit, the highest bit indicates C1. the hundreds and tens of thousands of digits are C2 and C3, And the percentile and the ten thousandths are C0 and BF.

If the highest bit of a negative number is a single bit, the highest bit indicates that the bits are 3E, the hundred bits and tens of thousands of BITs are 3D and 3C, And the percentile and ten-minute bits are 3f and 40 in turn.

1.2 Data Section

Each bit in the Data Section represents two digits. The two digits may be from 0 to 99. If the data itself is a positive number, the values are expressed in binary 1 to 64. If the data itself is a negative number, the values are represented in binary 65 to 2.

Note: In the data part, the value is also 0. In the positive value, the value 0x01 is used, and in the negative value, the value 0x65 is used.

1.3 symbol bits

Only when the number is negative can there be a sign bit. The symbol bit is expressed as 66.

Note: 0x66 is used, because the same number represents 0x66 in positive and negative numbers. For example, the data part of 1 is 0x02, and the data part of-1 is 0x64. The sum of the two numbers is 0x66.

2. Experiment Process

The above are summarized by using the dump results. For the relational constants mentioned above, Oracle makes a reasonable choice. We can also deduce them based on the example below, it will further explain why this method is used. What is listed here is to give you a rough understanding of the number type data.

The following is an example:

SQL> Create Table test_number (number_col number );

Table created

SQL> insert into test_number values (0 );

1 row inserted

SQL> insert into test_number values (1 );

1 row inserted

SQL> insert into test_number values (2 );

1 row inserted

SQL> insert into test_number values (25 );

1 row inserted

SQL> insert into test_number valuees (123 );

1 row inserted

SQL> insert into test_number valuees (4100 );

1 row inserted

SQL> insert into test_number valuees (132004078 );

1 row inserted

SQL> insert into test_number valuees (2.01 );

1 row inserted

SQL> insert into test_number valuees (0.3 );

1 row inserted

SQL> insert into test_number valuees (0.00000125 );

1 row inserted

SQL> insert into test_number valuees (115.200003 );

1 row inserted

SQL> insert into test_number values (-1 );

1 row inserted

SQL> insert into test_number values (-5 );

1 row inserted

SQL> insert into test_number values (-20032 );

1 row inserted

SQL> insert into test_number values (-234.432 );

1 row inserted

SQL> commit;

Commit complete

SQL> Col d_number format A50

SQL> select number_col, dump (number_col, 16) d_number from test_number;

Number_col d_number

------------------------------------------------------------

0 typ = 2 Len = 1: 80

1 typ = 2 Len = 2: C1, 2

2 typ = 2 Len = 2: C1, 3

25 typ = 2 Len = 2: C1, 1a

123 typ = 2 Len = 3: C2

4100 typ = 2 Len = 2: C2, 2a

132004078 typ = 2 Len = 6: C5, 4f

2.01 typ = 2 Len = 3: C1, 3, 2

0.3 typ = 2 Len = 2: C0, 1f

1.25e-6 typ = 2 Len = 3: Be, 2, 1a

115.200003 typ = 2 Len = 6: C2, 4

-1 typ = 2 Len = 3: 3E, 64, 66

-5 typ = 2 Len = 3: 3E, 60, 66

-20032 typ = 2 Len = 5: 3C

-234.432 typ = 2 Len = 6: 3D, 3A

15 rows selected

3. Conclusion

The following describes each line based on the results of the example. First, describe the two basics. Type = 2 returned by the dump function indicates that the data type of dump is number, and length = n indicates that the length of the value stored in the database is N.

3.1 dump (0)

The result of dump (0) is 0x80. As mentioned earlier, 0 only indicates a high bit and no data bit. Because 0 is special, it neither belongs to a positive number nor a negative number, it is enough to use a high value to indicate a bit with 80 and will not conflict with other data, oracle saves some of the subsequent data for space saving considerations. But why does Oracle select 0x80 to indicate 0? We know that positive numbers and negative numbers are the opposite. Each positive number has a corresponding negative number. Therefore, if we want to use the encoding to represent a value, the encoding that represents a positive number and a negative number should be half of each other, so that Oracle can ensure that the data range is reasonable. The binary encoding of 0x80 is 1000.
0000 is exactly half of the maximum Byte encoding value. Therefore, Oracle chooses 0x80 to indicate 0, which makes sense.

3.2 dump (1)

The result of dump (1) is 0xc102, 0xc1 indicates the highest bit, and 0x2 indicates the value is 1. First, why does Oracle use C1 to represent a single position? In fact, the truth is similar to what we just mentioned. Using scientific notation, any real number S can be described as a. B × 10n. A represents the integer part, B Represents the fractional part, and N represents the exponent part of 10. When S is greater than 1, n is greater than or equal to 0, and S is less than 1, n is less than 0. That is to say, in an exponential way, Oracle represents the widest range when n is greater than 0 and N is less than 0. Therefore, Oracle selects C1 to indicate that a single bit is the highest bit.

SQL> select to_char (round (to_number ('81 ', 'xxx') + (to_number ('ff', 'xxx')-to_number ('81 ', 'xxx ') + 1)/2), 'xx') value from dual;

Value

-----

C1

Why Does Oracle use 0x2 for 1 instead of 0x1 for 1? Each Oracle byte represents two digits. Therefore, for these two digits, it may be 0 ~ There are 100 possibilities in 99, and the problem lies in 0.

Oracle is implemented in C language at the underlying layer. We know that binary 0 is used as the string terminator in C language. Oracle uses 0x1 to avoid this problem, and so on, 0x64 indicates 99.

3.3 dump (2)

The result of dump (2) is 0xc103.

3.4 dump (25)

Dump (25) returns 0xc11a. As mentioned above, the data is saved in the smallest unit of two bits. Therefore, for 25, the highest bit indicates that the bit is still a single bit, and the value of the single bit is 25. According to the rule described above, 25 is stored as 0xc11a.

SQL> select to_char (25 + 1, 'xx') value from dual;

Value

-----

1a

3.5 dump (123)

Dump (123) returns 0xc20218. Because the maximum value of 123 is bits, the highest bit indicates that the bit is 0xc2, the hundred bits are 1, The 0x02 is used, the single bit is 23, and the 0x18 is used.

3.6 dump (4100)

Dump (4100) returns 0xc22a.

Note that if the last digit of a number is 0, Oracle will not store it for space saving. For example, 4100 only saves of BITs and of BITs, and only saves 51 bits and 20 bits.

3.7 dump (132004078)

Dump (132004078) returns 0xc5022101294f. The highest bit is bits, so 0xc5 is used for representation. 1 is represented by 0x02 on bits, 32 is represented by 0x21 on bits, and 0 is represented by 0x01 on bits, for a hundred bits, 40 is represented by 0x29, and for a single bits, 78 is represented by 0x4f.

Note: The value 0 of the intermediate digit cannot be omitted.

3.8 dump (2.01)

Dump (2.01) returns 0xc10302. The highest bit is represented by a single digit in 0xc1, the second in a single digit in 0x03, and the second in a percentile in 0x02.

Note: The following one digit is a percentile, not a very digit.

3.9 dump (0.3)

Dump (0.3) returns 0xc01f. The highest bit is the percentile, which is represented by 0xc0, And the percentile is 30 with 0x1f.

3.10 dump (0.00000125)

Dump (0.00000125) returns 0xbe021a. The maximum bit is a million-bit value, represented by 0xbe. 1 on the highest bit is represented by 0x02, and 25 is represented by 0x1a.

3.11 dump (115.200003)

Dump (115.200003) returns 0xc20210150104.

3.12 dump (-1)

The result of dump (-1) is 0x3e6466. The highest bit, represented by 0x3e. 64 indicates that 1, 66 indicates the symbol bit, indicating that the number is negative.

A negative number is the opposite of a positive number. The highest bit of a negative number indicates that the highest bit of a negative number is ff. The highest bit of 1 indicates C1, and the highest bit of-1 indicates 3E. In the negative number, 1 is represented by 64. The value in the negative number is 0x66, which is the symbol bit. Positive number 1 is represented by 0x02, negative number 1 is represented by 0x64, and the sum of the two is 0x66. A negative number has multiple identifiers, expressed as 0x66. Because the positive value ranges from 0x01 to 0x64, the negative value ranges from 0x65 to 0x02. Therefore, 0x66 is not displayed when the number is represented.

3.13 dump (-5)

The result of dump (-5) is 0x3e6066. 0x3e indicates that the highest bit is a single bit, 0x60 indicates that the top bit is 5, and 0x66 indicates the symbol identification bit. 0x3e and 0xc1 are 0xff. The result of 0x60 and 0x06 is 0x66.

3.14 dump (-20032)

Dump (-20032) returns 0x3c63654566. The maximum bit is bits, and the positive tens of thousands of BITs is 0xc3. Therefore, the negative tens of thousands of BITs is 0x3c. The value is 2 on bits, 0 on 0, 0 on bits, 0 on 0, 0 on 0, 0 on 0, and 0 on 1 bits, 32 on 1 bits, positive values are represented by 0x21, and negative values are represented by 0x45. 0x66 is a negative number.

3.15 dump (-234.432)

Dump (-234.432) returns 0x3d63433a5166.

 

Paste: http://www.itpub.net/forum.php? MoD = viewthread & tid = 308317.