Implementation of Oracle Number-type numerical storage and conversion

Oracle stores data through corresponding algorithm conversion in the database. This article briefly introduces the Number-type data storage and conversion of Oracle. This article aims to answer question No. 2119 on the message board.
We can use the DUMP function to convert the storage format of numbers. A simple output is similar to the following format:
Copy codeThe Code is as follows:
SQL> select dump (1) from dual; DUMP (1)
------------------
Typ = 2 Len = 2: 193,2

The DUMP function output format is similar:
Type <[length]>, symbol/exponent digit [number 1, number 2, number 3, ......, number 20]
The meanings are as follows:
1. Type: Number Type, Type = 2 (Type code can be found in the Oracle document)
2. Length: refers to the number of bytes stored
3. symbol/index bit
On storage, Oracle stores and converts positive and negative numbers respectively:
Positive: Add 1 storage (to avoid Null)
Negative number: It is reduced by 101. If the total length is smaller than 21 bytes, the last 102 is added (required for sorting)
Exponential conversion:
Positive number: exponent = symbol/exponent bit-193 (1 indicates positive number)
Negative: Index = 62-first byte
4. Start with <number 1> as a valid data bit.
The value is the highest valid bit starting from <number 1>. The stored numerical calculation method is as follows:
Add the following calculation results:
Each <digit> multiplied by 100 ^ (exponential-N) (N is the order of the valid digits, N = 0 for the first valid digit)
5. Examples
Copy codeThe Code is as follows:
SQL & gt; select dump (123456.789) from dual; DUMP (123456.789)
-------------------------------
Typ = 2 Len = 6: 195, 13, 35, 57, 79,91

<Index>: 195-193 = 2
<Number 1> 13-1 = 12*100 ^ (2-0) 120000
<Number 2> 35-1 = 34*100 ^ (2-1) 3400
<Number 3> 57-1 = 56*100 ^ (2-2) 56
<Number 4> 79-1 = 78*100 ^ (2-3). 78
<Number 5> 91-1 = 90*100 ^ (2-4). 009
123456.789
Copy codeThe Code is as follows:
SQL> select dump (-123456.789) from dual; DUMP (-123456.789)
----------------------------------
Typ = 2 Len = 7: 60, 89, 67,45, 102

<Index> 62-60 = 2 (the highest digit is 0, indicating a negative number)
<Number 1> 101-89 = 12*100 ^ (2-0) 120000
<Number 2> 101-67 = 34*100 ^ (2-1) 3400
<Number 3> 101-45 = 56*100 ^ (2-2) 56
<Number 4> 101-23 = 78*100 ^ (2-3). 78
<Number 5> 101-11 = 90*100 ^ (2-4 ).
123456.789 (-)
Now let's take another look at why adding 102 in the last step is required for sorting.-123456.789 is actually stored in the database

-123456.78901 the actual storage in the database is
, 91
It can be seen that if 102 is not added at the end,-123456.789 <-123456.78901 may occur during sorting.
For question No. 2119, the first question is:
1. Why does 193 and 2 mean?
You can see the answer from above.
2. Why are there two bytes of length for the NUMBER type?
For this problem, I think we should know that all data types are ultimately stored in binary in the computer. In fact, the so-called data types are defined. Therefore, the storage is only determined by the algorithm.
Therefore, this problem is not true. For example:
Copy codeThe Code is as follows:
SQL & gt; select dump (110) from dual; DUMP (110)
---------------------
Typ = 2 Len = 3: 194,2, 11SQL> select dump (1100) from dual; DUMP (1100)
-------------------
Typ = 2 Len = 2: 194,12

We can see that although 1100> 110, the storage of 1100 occupies only 2 bytes, while 110 occupies 3 bytes.