[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 -----
--//可以发现与前面单独测试都一样.看来我以前思考问题简单化了.^_^.