oracle中dump函式及oracle NUMBER型別內部儲存機制
oracle中dump函式
轉自:http://blog.vsharing.com/nimrod/A654847.html
DUMP函式的輸出格式類似:
型別 ,符號/指數位 [數字1,數字2,數字3,......,數字20]
各位的含義如下:
1.型別: Number型,Type=2 (型別程式碼可以從Oracle的文件上查到)
2.長度:指儲存的位元組數
3.符號/指數位
在儲存上,Oracle對正數和負數分別進行儲存轉換:
正數:加1儲存(為了避免Null)
負數:被101減,如果總長度小於21個位元組,最後加一個102(是為了排序的需要)
指數位換算:
正數:指數=符號/指數位 - 193 (最高位為1是代表正數)
負數:指數=62 - 第一位元組
4.從開始是有效的資料位
從開始是最高有效位,所儲存的數值計算方法為:
將下面計算的結果加起來:
每個乘以100^(指數-N) (N是有效位數的順序位,第一個有效位的N=0)
5、舉例說明
SQL> select dump(123456.789) from dual; DUMP(123456.789) |
: 195 - 193 = 2
13 - 1 = 12 *100^(2-0) 120000
35 - 1 = 34 *100^(2-1) 3400
57 - 1 = 56 *100^(2-2) 56
79 - 1 = 78 *100^(2-3) .78
91 - 1 = 90 *100^(2-4) .009
123456.789
SQL> select dump(-123456.789) from dual; DUMP(-123456.789) |
62 - 60 = 2(最高位是0,代表為負數)
101 - 89 = 12 *100^(2-0) 120000
101 - 67 = 34 *100^(2-1) 3400
101 - 45 = 56 *100^(2-2) 56
101 - 23 = 78 *100^(2-3) .78
101 - 11 = 90 *100^(2-4) .009
123456.789(-)
現在再考慮一下為什麼在最後加102是為了排序的需要,-123456.789在資料庫中實際儲存為
60,89,67,45,23,11
而-123456.78901在資料庫中實際儲存為
60,89,67,45,23,11,91
可見,如果不在最後加上102,在排序時會出現-123456.789
轉自
Internal representation of the NUMBER datatype
As with other datatypes, stored numbers are preceded by a length byte which stores the size of the datum in bytes, or 0xFF for NULLs. The actual data bytes for non-null numbers represent the value in scientific notation. For example, the number 12.3 is represented as +0.123 * 10². The high order bit of the first byte represents the sign. The sign bit is set for positive numbers; and clear for negative numbers. The remainder of the first byte represents the exponent, and then up to 20 bytes may be used to represent the significant digits excluding trailing zeros. This is sometimes called the mantissa.
Each byte of the mantissa normally represents two decimal digits. For positive numbers an offset of 1 is added to avoid null bytes, while for negative numbers an offset of 101 is added to the negated digit pair. Thus a mantissa byte with the decimal value of 100 might represent the digit pair "99" in a positive number, or the digit pair "01" in a negative number. The interpretation must be based on the sign bit. Negative numbers with less than 20 mantissa bytes also have a byte with the (impossible) decimal value 102 appended. I don't know what purpose this serves.
If there are an odd number of significant digits before the decimal point, the first mantissa byte can only represent 1 digit because the decimal exponent must be even. In this case, the 20-byte mantissa can represent at most 39 decimal digits. However, the last digit may not be accurate if a more precise value has been truncated for storage. This is why the maximum guaranteed precision for Oracle numbers is 38 decimal digits, even though 40 digits can be represented.
The decimal exponent is guaranteed to be even by the alignment of the mantissa. Thus the value stored for the exponent is always halved and is expressed such that the decimal point falls before the first digit of the mantissa. It again represents a pair of decimal digits, this time with an offset of 64 for positive numbers, and 63 for the negated exponent of negative numbers. Thus a set of exponent bits with the decimal value of 65 might represent the exponent +2 in a positive number, or the exponent -4 in a negative number. Please note that the encoding of the exponent is based on the sign of the number, and not on the sign of the exponent itself.
Finally, there are special encodings for zero, and . Zero is represented by the single byte 0x80. Negative infinity is represented by 0x00, and positive infinity is represented by the two bytes 0xFF65. These are illustrated in the listing below.
SQL> select n, dump(n,16) from special_numbers; N DUMP(N,16) --- ------------------------------------------ 0 Typ=2 Len=1: 80 -~ Typ=2 Len=1: 0 ~ Typ=2 Len=2: ff,65
For the rest, the best way to familiarize yourself further with the internal representation of numbers is to use the dump function to examine the representation of some sample values. This is simulated below. Just type a number and then press "Enter" to check out its representation. For example, try to find out why 110 takes one more byte of storage than 1100 despite being a smaller number.
Number: | ||
Representation: |
|
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-591127/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE NUMBER型別內部實現Oracle型別
- 【NUMBER】Oracle資料庫最佳化之理解NUMBER儲存機制Oracle資料庫
- Oracle 儲存型別Oracle型別
- Oracle中number型別詳解Oracle型別
- oracle dump 函式Oracle函式
- Oracle dump函式Oracle函式
- Oracle的dump函式Oracle函式
- oracle中number型欄位長度、精度及實際儲存狀態測試(zt)Oracle
- 數值在Oracle的內部儲存Oracle
- Oracle Lob型別儲存淺析Oracle型別
- Oracle dump函式的用法Oracle函式
- ORACLE NUMBER資料型別Oracle資料型別
- SQL學習筆記(ORACLE內部儲存)SQL筆記Oracle
- 在Oracle中查詢儲存過程和函式Oracle儲存過程函式
- oracle內部轉換函式雜談Oracle函式
- [20190930]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- [20191013]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- Android中關於內部儲存的一些重要函式Android函式
- Oracle的number資料型別Oracle資料型別
- 【NUMBER】Oracle的NUMBER資料型別特點Oracle資料型別
- Oracle Library cache內部機制詳解Oracle
- ORACLE中的DUMP轉儲方法Oracle
- Oracle中DUMP轉儲方法 收藏Oracle
- Oracle中DUMP的轉儲方法Oracle
- Oracle 內部2,8,10,16進位制轉換函式包Oracle函式
- [20191003]oracle number型別儲存轉化指令碼.txtOracle型別指令碼
- oracle資料型別與儲存結構Oracle資料型別
- 關於ORACLE組合索引內部儲存淺談Oracle索引
- Oracle基本資料型別儲存格式淺析——RAW型別Oracle資料型別
- InnoDB儲存引擎鎖機制(二、 鎖的型別)儲存引擎型別
- oracle dump函式解析_字符集Oracle函式
- dump Oracle資料庫的內部結構Oracle資料庫
- Oracle 儲存過程 定義 和 優點 與 函式 區別Oracle儲存過程函式
- ORACLE DATE型別和TIMESTAMP型別DUMP換算Oracle型別
- 10_深入解析Oracle number資料型別及os層number解析工具分享Oracle資料型別
- Oracle 中的 ROW_NUMBER() OVER() 分析函式的用法Oracle函式
- 一個簡單函式—Number型別轉換Date型別函式型別
- Oracle基本資料型別儲存格式淺析(五)——RAW型別Oracle資料型別