Use the dump function to learn about Oracle Data Storage Methods

Problem:
1. How are data types such as number, Char, varchar, and date stored in Oracle? What is a byte stream?
2. What is the length of the byte used by various data types?

Select dump (1), dump (123456), dump (-123456), dump (1234567890123456) -- integer
-- Select dump (123456.789), dump (-123456.789), dump (1.234567890123456789e15), dump (123456789012345678901234567890123456789) -- floating point number
Select dump ('A'), dump ('abcdefghijklmn '), dump (cast ('abc' as char (20 ))), dump (cast ('abc you' as varchar (20 )))
-- Select sysdate, dump (sysdate)
From dual;

Conclusion:
1. the byte stream of the number type is not long, and the positive and negative numbers use different signs (positive: Number 1> = 193, negative: Number 1 <= 62, the difference is exactly the index size)
2. Char is fixed length. If the length is insufficient, the maximum length will be filled with Trailing spaces. varchar is not fixed length (that is, the length of long for long)
3. The date type is fixed to 8 bytes.

-- The appendix describes the storage methods of common data types. For more information, see the official Oracle documentation or various technical blogs.
Http://blog.oracle.com.cn/index.php/12012/action_viewspace_itemid_4684.html (Source of appendix)
Http://zhouwf0726.itpub.net/post/9689/196733

During the experiment (oracle9i2), I found that the actual date type is different from that in the Appendix. It is estimated that the appendix describes the versions earlier than oracle8.

Date (length 7 Type 12)

Col dump_date form a35col real_date form A35
Select dump (last_ddl_time) dump_date, to_char (last_ddl_time, 'yyyy-mm-dd hh24: MI: ss') real_date
From user_objects
Where rownum = 1;

Dump_date real_date
--------------------------------------------------------
Typ = 12 Len = 7: 120,102, 120, 100, 48, 53 15:47:52 century 100-= 20th century and year plus post Storage
Year 102-100 = 2
Month/month/date stored by original value
Date 13
Hours 16-1 = 15 hours after adding 1 Storage
Minutes 48-1 = 47
Seconds 53-1 = 52
--------------------------------------------------------------------------------

Number (Type 2)

<[Length]>, symbol bit/index number 1, number 2, number 3, ......, number 20

Positive number: exponent = Number 1-193 (1 indicates positive number)
Negative: Index = 62-first byte

Number 1 is the highest valid bit

Positive: plus 1 Storage
Negative number: It is reduced by 101. If the total length is smaller than 21 bytes, the last 102 is added (required for sorting)

The stored value calculation method is as follows:

Add the following calculation results:

Multiply each number by 100 ^ (exponential-N) (N is the order of the number of valid digits, n = 0 for the first valid digit)

Example:

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*1002-0 120000
Number 2 35-1 = 34*1002-1 3400
Number 3 57-1 = 56*1002-2 56
Number 4 79-1 = 78*1002-3. 78
Number 5 91-1 = 90*1002-4. 009
123456.789

Select dump (-123456.789) from dual;

Dump (-1, 123456.789)
----------------------------------
Typ = 2 Len = 7: 60, 89, 67,45, 102, 11, index 62-60 = 2 (the highest bit is 0, representing a negative number)
Number 1 101-89 = 12*1002-0 120000
Number 2 101-67 = 34*1002-1 3400
Number 3 101-45 = 56*1002-2 56
Number 4 101-23 = 78*1002-3. 78
Number 5 101-11 = 90*1002-4. 009
123456.789 (-)

Now let's take another look at why adding 102 in the end is required for sorting.-123456.789 is actually stored in the database as 60, 89 ,.
The actual storage of-123456.78901 in the database is 60, 89, 67,45, and 91.
It can be seen that if 102 is not added at the end,-123456.789 <-123456.78901 may occur during sorting.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/luocm/archive/2007/07/12/1686434.aspx