Date (length 7 type 12) col dump_date form a35col real_date form a35select dump (last_ddl_time) dump_date, to_char (last_ddl_time, 'yyyy-mm-dd hh24: mi: ss') real_datefrom user_objectswhere rownum = 1; DUMP_DATE REAL_DATE ----- ------------------------------------------------------------------------------------------------------------------------------------------------------------------ -Typ = 12 LEN = 7: 120, 102, 4, 13, 16, 48, 53 2002-04-13 15:47:52 Century 120 - 100 = 20th century and year plus 100 after storage Year 102 - 100 = February 4 The month and date is stored in the original value of 13 hours 16 - 1 = 15 times plus 1 after the 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: Index = Number 1 - 193 (the highest bit is 1 is the positive number) negative number: Index = 62 - The first byte number 1 is the highest valid bit positive: plus 1 storage negative: 101 minus, if the total length is less than 21 words In the section, the numerical calculation method stored in the last 102 (which is required for sorting) is: add the result of the following calculation: Each number is multiplied by 100 ^ (index-N) (n is the sequential bit of the active bits) , The first active bit N = 0) 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 Digital 3 57 - 1 = 56 * 1002-2 56 Digital 4 79 - 1 = 78 * 1002-3.78 Digital 5 91 - 1 = 90 * 1002 -4.009 123456.789 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 position is 0, representing negative) Number 1 101 - 89 = 12 * 1002-0 120000 Digital 2 101 - 67 = 34 * 1002-1 3400 Digital 3 101 - 45 = 56 * 1002-2 56 Digital 4 101 - 23 = 78 * 1002-3 .78 Digital 5 101 - 11 = 90 * 1002-4.009 123456.789 (-) Now consider why the last plus 102 is required for sorting, -123456.789 actually stores 60, 89, 67, 45, 23, 11 in the database, and -123456.78901 actually stored in the database to 60, 89, 67, 45, 23, 11, 91 can be seen, if not in the last plus 102, it will appear when sorting - 123456.789 <
- 123456.78901. ---- The above part is repost ------
Look at DUMP's Data Block content: TL: 9 fb: --h-fl - lb: 0x0 cc: 2col 0: [2] C1 02COL 1: [2] 61 61TAB 0, ROW 1, @ 0x1f8etl: 9 FB: --H-fl - lb: 0x0 cc: 2col 0: [2] C1 03COL 1: [2] 61 61 Here is 16-en-format SQL> SELECT DUMP (ID), DUMP (NAME) , ID, Name from test_index; dump (ID) Dump (name) ID name ------------------------------- ------------------------------------------ TYP = 2 LEN = 2: 193, 2 TYP = 1 LEN = 2: 97, 97 1 aa type = 2 LEN = 2: 193, 3 TYP = 1 LEN = 2: 97, 97 2 AA From here seeing the data type of Column, ie The information of TYP is not reflected in the Block.
Discussion on some extensions of DATE storage:
SQL> CREATE TABLE TEST1 AS SELECT SYSDATE TEST_DATE ASDITEDSQL> SELECT DUMP (TEST_DATE) from Test1; Dump (Test_Date) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------- TYP = 12 LEN = 7: 120, 104, 8, 20, 12, 53, 47sql> Select Dump (TO_DATE (SYSDATE)) from DUAL; Dump (to_date (sysdate)) ----- -------------------------------------------------- ------------------------- TYP = 13 LEN = 8: 212, 7, 8, 20, 0, 0, 0 we see The two different results are different. This article is in the storage method of the data type in Oracle (ie Typ = 12) is the storage of Date, and the use of Oracle is somewhat different in Memory, so TYP = 13 here. Instead of 12.
