[20181124]关于降序索引问题3.txt

[20181124]关于降序索引问题3.txt --//链接：blog.itpub.net/267265/viewspace-2221425/，探讨降序索引中索引的键值。 --//实际上使用函数sys_op_descend. --//链接:http://blog.itpub.net/267265/viewspace-2221527/,探讨了仅仅设计字符串的编码. --//字符串0x00,0x0000,0x0001,0x00NN(0xNN>=0x02),0x01,0x0100,0x0101,0x01NN(0xNN>=0x02) 单独编码。画一个表格： ascii码                 编码 --------------------------------------------- 0x00                    FEFE 0x0000                  FEFD 0x0001                  FEFC 0x00NN(0xNN>=0x02)      FEFB 0x01                    FEFA 0x0100                  FEF9 0x0101                  FEF8         0x01NN(0xNN>=0x02)      FEF7 --------------------------------------------- --//对于numbe,date类型如何呢? --//我在没有测试前,感觉不会出现像字符串那样的编码,因为数据类型,日期类型保存格式规避0x00,这样不会出现像字符串那样的情况. --//还是通过测试说明问题. 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试: SCOTT@test01p> select sys_op_descend(10001),dump(10001,16) c30 from dual ; SYS_OP_DESCE C30 ------------ ------------------------------ 3CFDFEF7FDFF Typ=2 Len=4: c3,2,1,2 --//还是有点出乎我的意料,可以发现还是按照上面字符串编码的规律: 3C FD FEF7 FD c3 02 01  02 --//中间 01 编码 FEF7(按照0x01NN编码). SCOTT@test01p> select sys_op_descend(1000001),dump(1000001,16) c30 from dual ; SYS_OP_DESCE C30 ------------ ------------------------------ 3BFDFEF8FDFF Typ=2 Len=5: c4,2,1,1,2 3B FD FEF8 FD c4 02 0101 02 --//中间 0101 对应编码 0x0101. 3.继续测试日期看看: --//注意一个细节date类型,oracle存在2种类型(12,13),保存在数据库块中的类型是type=12. SCOTT@test01p> select dump(to_date('1980-12-17 00:00:00','yyyy-mm--dd hh24:mi:ss'),16) c40 ,dump(hiredate,16) c40 ,hiredate from emp where rownum=1 ; C40                                      C40                                      HIREDATE ---------------------------------------- ---------------------------------------- ------------------- Typ=13 Len=8: bc,7,c,11,0,0,0,0          Typ=12 Len=7: 77,b4,c,11,1,1,1           1980-12-17 00:00:00 --//可以发现type=12,时分秒都在原来的基础上+1,这样规避0x00.月份在1-12不会出现0的情况,不加1.日期在1-31,也是一样0的情况. --//一些细节可以看链接:http://blog.itpub.net/4227/viewspace-68514/ SCOTT@test01p> select sys_op_descend(hiredate) c40 ,dump(hiredate,16) c40,hiredate from emp where rownum=1 ; C40                                      C40                                      HIREDATE ---------------------------------------- ---------------------------------------- ------------------- 884BF3EEFEF8FEFAFF                       Typ=12 Len=7: 77,b4,c,11,1,1,1           1980-12-17 00:00:00 88 4B F3 EE FEF8 FEFA 77 b4 0c 11 0101 01 --//对照前面的编码都可以对上. 4.是否真实是这样呢?建立表测试看看: SCOTT@test01p> create table t ( id number,cr_date date); Table created. insert into t values (1,sysdate); insert into t values (10001,trunc(sysdate)); insert into t values (1000001,to_date('1980-12-17 00:00:00','yyyy-mm-dd hh24:mi:ss')); commit ; SCOTT@test01p> select * from t;         ID CR_DATE ---------- -------------------          1 2018-11-24 20:31:32      10001 2018-11-24 00:00:00    1000001 1980-12-17 00:00:00 --//分别看看降序索引的情况: SCOTT@test01p> create  index if_t_all on t(id ,id desc ,cr_date,cr_date desc); Index created. SCOTT@test01p> select segment_name,header_file,header_block from dba_segments where owner=user and segment_name in ('IF_T_ALL'); SEGMENT_NAME         HEADER_FILE HEADER_BLOCK -------------------- ----------- ------------ IF_T_ALL                      11          194 SCOTT@test01p> alter system flush buffer_cache; System altered. SCOTT@test01p> alter system dump datafile 11 block 195; System altered. --//检查转储文件: row#0[8003] flag: -------, lock: 0, len=33 col 0; len 2; (2):  c1 02 col 1; len 3; (3):  3e fd ff col 2; len 7; (7):  78 76 0b 18 15 20 21 col 3; len 8; (8):  87 89 f4 e7 ea df de ff col 4; len 6; (6):  02 c0 00 be 00 00 row#1[7964] flag: -------, lock: 0, len=39 col 0; len 4; (4):  c3 02 01 02 col 1; len 6; (6):  3c fd fe f7 fd ff col 2; len 7; (7):  78 76 0b 18 01 01 01 col 3; len 9; (9):  87 89 f4 e7 fe f8 fe fa ff col 4; len 6; (6):  02 c0 00 be 00 01 row#2[7924] flag: -------, lock: 0, len=40 col 0; len 5; (5):  c4 02 01 01 02 col 1; len 6; (6):  3b fd fe f8 fd ff col 2; len 7; (7):  77 b4 0c 11 01 01 01 col 3; len 9; (9):  88 4b f3 ee fe f8 fe fa ff col 4; len 6; (6):  02 c0 00 be 00 02 ----- end of leaf block Logical dump ----- --//可以发现与前面单独测试都一样.看来我以前思考问题简单化了.^_^.