Oracle in the database through the corresponding algorithm conversion for data storage, this paper briefly introduces the number of Oracle digital storage and conversion. This is to answer question number No. 2119 on the message board.
we can use the dump function to convert the stored form of a number, a simple output similar to the following format:
Copy Code code as follows:
Sql> Select Dump (1) from Dual;dump (1)
------------------
typ=2 len=2:193,2
The dump function has a similar output format:
Type <[length]>, symbol/digit [number 1, number 2, Number 3, ..., number 20]
you have the following meanings:
1. Type: Number type, type=2 (type code can be found from Oracle's documentation)
2. Length: Refers to the number of bytes stored
3. Symbol/exponent Position
On storage, Oracle stores conversions for positive and negative numbers, respectively:
Positive number: Add 1 storage (to avoid null)
Negative numbers: reduced by 101, if the total length is less than 21 bytes, and finally adds a 102 (is for sorting needs)
Digit Conversion:
Positive number: exponent = symbol/digit-193 (the highest bit is 1 to represent positive numbers)
Negative Number: index = 62-First byte
4. Starting from < Digital 1> is a valid data bit
Starting from the < Digital 1> is the most significant bit, and the stored numerical methods are:
Add up the results of the following calculations:
Each < digit bit > times 100^ (Exponent-N) (n is the ordinal bit of a significant number of digits, the n=0 of the first significant bit)
5. Examples
Copy Code code as follows:
Sql> Select Dump (123456.789) from Dual;dump (123456.789)
-------------------------------
typ=2 len=6:195,13,35,57,79,91
< index: 195-193 = 2
< digital 1> 13-1 = 12 *100^ (2-0) 120000
< digital 2> 35-1 = 34 *100^ (2-1) 3400
< digital 3> 57-1 = 56 *100^ (2-2) 56
< digital 4> 79-1 = 78 *100^ (2-3). 78
< digital 5> 91-1 = 90 *100^ (2-4). 009
123456.789
Copy Code code as follows:
Sql> Select Dump ( -123456.789) from Dual;dump (-123456.789)
----------------------------------
typ=2 len=7:60,89,67,45,23,11,102
< index > 62-60 = 2 (highest bit is 0, represented as negative)
< digital 1> 101-89 = 12 *100^ (2-0) 120000
< digital 2> 101-67 = 34 *100^ (2-1) 3400
< digital 3> 101-45 = 56 *100^ (2-2) 56
< digital 4> 101-23 = 78 *100^ (2-3). 78
< digital 5> 101-11 = 90 *100^ (2-4). 009
123456.789 (-)
Now consider why the last addition of 102 is needed for sorting,-123456.789 is actually stored in the database as
60,89,67,45,23,11
And-123456.78901 is actually stored in the database as
60,89,67,45,23,11,91
Visible, if not the last plus 102, in the sort will appear -123456.789<-123456.78901.
For question number No. 2119, the first question is:
1. Excuse me, why does the 193,2 mean anything?
You can see the answer from above.
2. There are number numbers. Why is there a 2-byte length?
For this question, I think we should know that all data types end up in binary storage on the computer, and that the so-called data types are all defined by us. So the store is only determined by the algorithm.
So this problem is not tenable. For example:
Copy Code code as follows:
Sql> Select Dump ($) 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'll see that, although 1100>110, 1100 of the storage is only 2 bytes, while 110 takes up 3 bytes.
