[20200317]NULL与排序输出.txt
--//NULL如果保存在oracle数据库的块中编码是0xFF,没有长度指示器,注:如果1个表后面的字段都是NULL.oracle选择不保存这些NULL.
--//这样如果这个字段参与排序一般情况下一定在普通索引块的最后,注如果索引键值全部为NULL不保存在普通索引块中.
--//但是如果插入值是0xFF呢?情况如何呢?
1.环境:
SCOTT@book> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t (id number,vc varchar2(20),idx number);
insert into t values (1,'A',1);
insert into t values (1,'AA',2);
insert into t values (1,chr(255)||'A',3);
insert into t values (1,chr(254)||'A',4);
insert into t values (1,chr(255)||'AB',5);
insert into t values (1,NULL,6);
commit ;
2.测试:
SCOTT@book> alter session set statistics_level = all;
Session altered.
SCOTT@book> set null NULL
SCOTT@book> select * from t order by id,vc;
ID VC IDX
---------- -------------------- ----------
1 A 1
1 A 4
1 AA 2
1 A 3
1 AB 5
1 NULL 6
6 rows selected.
--//你可以发现NULL排序在最后。另外发现chr(254)字符没有插入，这个问题先放一边。
Plan hash value: 961378228
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 6 |00:00:00.01 | 6 | | | |
| 1 | SORT ORDER BY | | 1 | 6 | 54 | 4 (25)| 00:00:01 | 6 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS FULL| T | 1 | 6 | 54 | 3 (0)| 00:00:01 | 6 |00:00:00.01 | 6 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
3.转储块看看：
SCOTT@book> select rowid from t where rownum=1;
ROWID
------------------
AAAWK6AAEAAAALkAAA
SCOTT@book> @ rowid AAAWK6AAEAAAALkAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90810 4 740 0 0x10002E4 4,740 alter system dump datafile 4 block 740 ;
SCOTT@book> alter system checkpoint ;
System altered.
--//采用bbed观察，好久不用了,有点生疏^_^。
BBED> set dba 4,740
DBA 0x010002e4 (16777956 4,740)
BBED> p kdbr
sb2 kdbr[0] @118 8077
sb2 kdbr[1] @120 8065
sb2 kdbr[2] @122 8053
sb2 kdbr[3] @124 8042
sb2 kdbr[4] @126 8029
sb2 kdbr[5] @128 8019
BBED> x /6rnxn *kdbr[5]
rowdata[0] @8119
----------
flag@8119: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8120: 0x01
cols@8121: 3
col 0[2] @8122: 1
col 1[0] @8125: *NULL*
col 2[2] @8126: 6
--//NILL在这里。注:oracle数据插入很像往水桶里面灌水,底部的数据是第一条记录。
BBED> dump /v offset 8119 count 12
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 740 Offsets: 8119 to 8130 Dba:0x010002e4
-----------------------------------------------------------------------------------------------------------
2c010302 c102ff02 c1072c01 l ,.........,.
~~~~~~!!!!!!!@@#######
<32 bytes per line>
---// 02c102 => 1 ，前面的02表示占2个字节，实际1的编码是c102。NULL编码是0xff，没有前面的长度指示器，占用1个字节。
rowdata[10] @8129
-----------
flag@8129: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8130: 0x01
cols@8131: 3
col 0[2] @8132: 1
col 1[3] @8135: 0xff 0x41 0x42
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col 2[2] @8139: 5
--//vc= chr(255)||'AB',编码是0xff 0x41 0x42,为什么排序这个编码在NULL的前面呢？
rowdata[23] @8142
-----------
flag@8142: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8143: 0x01
cols@8144: 3
col 0[2] @8145: 1
col 1[1] @8148: 0x41
col 2[2] @8150: 4
rowdata[34] @8153
-----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x01
cols@8155: 3
col 0[2] @8156: 1
col 1[2] @8159: 0xff 0x41
col 2[2] @8162: 3
rowdata[46] @8165
-----------
flag@8165: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8166: 0x01
cols@8167: 3
col 0[2] @8168: 1
col 1[2] @8171: 0x41 0x41
col 2[2] @8174: 2
rowdata[58] @8177
-----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x01
cols@8179: 3
col 0[2] @8180: 1
col 1[1] @8183: 0x41
col 2[2] @8185: 1
4.建立索引转储看看。
SCOTT@book> create index i_t_id_vc on t(id,vc);
Index created.
SCOTT@book> @ seg scott.i_t_id_vc
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- ----- ------------ ------------- ------------ ------------------- ------ ---------- ----------
0 SCOTT I_T_ID_VC NULL INDEX USERS 8 4 746
--//索引很小，数据都在根节点，下一个块dba=4,747就是索引的根节点。转储看看。
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system dump datafile 4 block 747 ;
System altered.
--//转储内容如下：
Block header dump: 0x010002eb
Object id on Block? Y
seg/obj: 0x162bb csc: 0x03.1778169c itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x10002e8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.1778169c
Leaf block dump
===============
header address 139660382480484=0x7f05377ca864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 0
kdxconro 6
kdxcofbo 48=0x30
kdxcofeo 7945=0x1f09
kdxcoavs 7897
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8018] flag: ------, lock: 0, len=14
col 0; len 2; (2): c1 02
col 1; len 1; (1): 41
col 2; len 6; (6): 01 00 02 e4 00 00
row#1[8004] flag: ------, lock: 0, len=14
col 0; len 2; (2): c1 02
col 1; len 1; (1): 41
col 2; len 6; (6): 01 00 02 e4 00 03
row#2[7989] flag: ------, lock: 0, len=15
col 0; len 2; (2): c1 02
col 1; len 2; (2): 41 41
col 2; len 6; (6): 01 00 02 e4 00 01
row#3[7974] flag: ------, lock: 0, len=15
col 0; len 2; (2): c1 02
col 1; len 2; (2): ff 41
col 2; len 6; (6): 01 00 02 e4 00 02
row#4[7958] flag: ------, lock: 0, len=16
col 0; len 2; (2): c1 02
col 1; len 3; (3): ff 41 42
col 2; len 6; (6): 01 00 02 e4 00 04
row#5[7945] flag: ------, lock: 0, len=13
col 0; len 2; (2): c1 02
col 1; NULL
col 2; len 6; (6): 01 00 02 e4 00 05
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 747 maxblk 747
--//我开始以为排序包括前面长度指示器，想想不对，因为加入输入字符B 以及AA，如果包括长度部分
--// B=>01 42， AA => 02 41 41 ,这样B在前，显然不对。
--//如果排序包括前面长度指示器，采用值在前面，长度在后的方式也是不对。这样NULL还是在chr(255)||'A'的前面。
--//我只能理解NULL在排序时oracle做了特殊处理。那位能给出更好的解析以及建议，谢谢。
--//补充测试：
SCOTT@book> insert into t values (1,'BB'||chr(254)||'A',7);
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select * from t order by id,vc;
ID VC IDX
---------- -------------------- ----------
1 A 1
1 A 4
1 AA 2
1 BBA 7
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 A 3
1 AB 5
1 NULL 6
7 rows selected.
--//很明显chr(254)被忽略掉了。0xfe对应 索引的 TERM编码是0xfe，参考连接：http://blog.itpub.net/267265/viewspace-2656689/.
SCOTT@book> select dump(vc,16) c30 ,idx from t order by id,vc;
C30 IDX
------------------------------ ----------
Typ=1 Len=1: 41 1
Typ=1 Len=1: 41 4
Typ=1 Len=2: 41,41 2
Typ=1 Len=3: 42,42,41 7
Typ=1 Len=2: ff,41 3
Typ=1 Len=3: ff,41,42 5
NULL 6
7 rows selected.
[20200317]NULL与排序输出.txt
-
lfree
2020-03-18 09:38:27 -
Oracle
- 原创