[20200317]NULL與排序輸出.txt

lfree發表於2020-03-18

[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.


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2681057/,如需轉載,請註明出處,否則將追究法律責任。

相關文章