[20181123]關於降序索引問題.txt

--//以前寫的連結:http://blog.itpub.net/267265/viewspace-1159181/

--//降序索引實際上dump值的每個值與0xff異或,最後新增0xff.這樣索引鍵值長度比普通索引長度+1.

--//NULL實際上對應0xff,這樣異或後變成0x00.

--//有幾個疑問.1.如果字串長度已經是4000字元如何處理.2.如果插入chr(0),對應鍵值是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

2.建立測試:

create table t (id number,name varchar2(4000));

insert into t values (1,'aaaaaa');

insert into t values (2,'bbbbbb');

insert into t values (3,chr(0));

insert into t values (4,lpad('c',4000,'c'));

commit ;

create index i_t_name on t(name desc);

SCOTT@book> select segment_name,header_file,header_block from dba_segments where owner=user and segment_name in ('I_T_NAME');

SEGMENT_NAME HEADER_FILE HEADER_BLOCK

-------------------- ----------- ------------

I_T_NAME 4 554

--//記錄很少，僅僅只有一個索引根節點。

SCOTT@book> alter system dump datafile 4 block 555;

System altered.

3.檢查轉儲:

Block header dump: 0x0100022b

Object id on Block? Y

seg/obj: 0x1610c csc: 0x03.376e0cf9 itc: 2 flg: E typ: 2 - INDEX

brn: 0 bdba: 0x1000228 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.376e0cf9

Leaf block dump

===============

header address 140469718254180=0x7fc1a7a88264

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 4

kdxcofbo 44=0x2c

kdxcofeo 3974=0xf86

kdxcoavs 3930

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8032

row#0[4021] flag: ------, lock: 0, len=4011

col 0; len 4000; (4000):

9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c

...

9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c 9c

--//可以發現如果字串長度等於4000,後面的0xff不存在.

col 1; len 6; (6): 01 00 02 24 00 03

row#1[4004] flag: ------, lock: 0, len=17

col 0; len 7; (7): 9d 9d 9d 9d 9d 9d ff

col 1; len 6; (6): 01 00 02 24 00 01

row#2[3987] flag: ------, lock: 0, len=17

col 0; len 7; (7): 9e 9e 9e 9e 9e 9e ff

col 1; len 6; (6): 01 00 02 24 00 00

row#3[3974] flag: ------, lock: 0, len=13

col 0; len 3; (3): fe fe ff

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

col 1; len 6; (6): 01 00 02 24 00 02

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 555 maxblk 555

SCOTT@book> select dump(name,16) c30 from t where id <=3;

C30

------------------------------

Typ=1 Len=6: 61,61,61,61,61,61

Typ=1 Len=6: 62,62,62,62,62,62

Typ=1 Len=1: 0

--//奇怪的是chr(0),降序排序後變成了fe fe ff.長度變成了3.看來我以前想的過於簡單了.

SCOTT@book> create index i_t_namex on t(name);

Index created.

SCOTT@book> select segment_name,header_file,header_block from dba_segments where owner=user and segment_name in ('I_T_NAMEX');

SEGMENT_NAME HEADER_FILE HEADER_BLOCK

-------------------- ----------- ------------

I_T_NAMEX 4 562

SCOTT@book> alter system dump datafile 4 block 563;

System altered.

Block header dump: 0x01000233

Object id on Block? Y

seg/obj: 0x1610e csc: 0x03.376e112c itc: 2 flg: E typ: 2 - INDEX

brn: 0 bdba: 0x1000230 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.376e112c

Leaf block dump

===============

header address 140469718254180=0x7fc1a7a88264

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 4

kdxcofbo 44=0x2c

kdxcofeo 3978=0xf8a

kdxcoavs 3934

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8032

row#0[8021] flag: ------, lock: 0, len=11

col 0; len 1; (1): 00

col 1; len 6; (6): 01 00 02 24 00 02

row#1[8005] flag: ------, lock: 0, len=16

col 0; len 6; (6): 61 61 61 61 61 61

col 1; len 6; (6): 01 00 02 24 00 00

row#2[7989] flag: ------, lock: 0, len=16

col 0; len 6; (6): 62 62 62 62 62 62

col 1; len 6; (6): 01 00 02 24 00 01

row#3[3978] flag: ------, lock: 0, len=4011

col 0; len 4000; (4000):

63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63

...

63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63

col 1; len 6; (6): 01 00 02 24 00 03

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 563 maxblk 563

3.重新測試:

create table tx (id number,name varchar2(10));

insert into tx values (0,chr(0));

insert into tx values (1,chr(1));

insert into tx values (2,chr(2));

insert into tx values (3,chr(97));

commit ;

SCOTT@book> create index if_tx_name on tx(name desc,name);

Index created.

SCOTT@book> select segment_name,header_file,header_block from dba_segments where owner=user and segment_name in ('IF_TX_NAME');

SEGMENT_NAME HEADER_FILE HEADER_BLOCK

-------------------- ----------- ------------

IF_TX_NAME 4 682

SCOTT@book> alter system flush buffer_cache;

System altered.

SCOTT@book> alter system dump datafile 4 block 683;

System altered.

--//檢查轉儲:

row#0[8018] flag: ------, lock: 0, len=14

col 0; len 2; (2): 9e ff

col 1; len 1; (1): 61

col 2; len 6; (6): 01 00 02 a4 00 03

row#1[8004] flag: ------, lock: 0, len=14

col 0; len 2; (2): fd ff

col 1; len 1; (1): 02

col 2; len 6; (6): 01 00 02 a4 00 02

row#2[7989] flag: ------, lock: 0, len=15

col 0; len 3; (3): fe fa ff

col 1; len 1; (1): 01

col 2; len 6; (6): 01 00 02 a4 00 01

row#3[7974] flag: ------, lock: 0, len=15

col 0; len 3; (3): fe fe ff

col 1; len 1; (1): 00

col 2; len 6; (6): 01 00 02 a4 00 00

--//要表達chr(0)為什麼是fe fe,真是再次考驗自己的中文表達能力^_^.

1.首先1點為什麼最後加上0xff,實際上為了排序的需要,假設2個字串排序'a','aa',如果正常排序'a','aa'.

降序排序就是'aa'在前,'a'在後.如果沒有0xff在最後,'aa'=>'0x9e9e','a'=>'0x9e',這樣排序變成'a'在前,'aa'在後.

最後補上'0xff'後,'保證'a'在'aa'之後(指編碼之後).aa'=>'0x9e9eff','a'=>'0x9eff',這樣降序排序保證'aa'在前,'a'在後.因為

0xff是最大的ascii碼. 這也是為什麼降序排序最後要加上0xff.

2.這樣問題就來了,假設排序字串'a\0'(實際上就是'a'||chr(0)).這樣如果按照前面的編碼問題就來了.

'a\0'對應編碼就是'0x9effff','a'=>'0x9eff'.這樣降序排序有變成了'a'在前,'a\0'在後,違背降序排序的原則.

為了規避這個風險,oracle選擇'0xfefe'表示chr(0)的降序.這樣chr(1)就不能再是'0xfe',變成了'0xfefa',

至於為什麼不是'0xfefd','0xfefc',我就不知道了.

--//另外實際上降序使用的函式是sys_op_descend,反向是SYS_OP_UNDESCEND,可以透過執行計劃看出來.

SCOTT@book> select /*+ index(t) */ count(*) from tx where name=chr(0);

COUNT(*)

----------

1

SCOTT@book> @ dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID 4t3j0dhf5qg2c, child number 0

-------------------------------------

select /*+ index(t) */ count(*) from tx where name=chr(0)

Plan hash value: 4016183490

---------------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 1 (100)| |

| 1 | SORT AGGREGATE | | 1 | 7 | | |

|* 2 | INDEX RANGE SCAN| IF_TX_NAME | 1 | 7 | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1 / TX@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("TX"."SYS_NC00003$"=HEXTORAW('FEFEFF') )

filter(SYS_OP_UNDESCEND("TX"."SYS_NC00003$")=')

--//執行計劃實際上包括access以及filter.

SCOTT@book> select sys_op_descend(chr(rownum-1)) from dual connect by level<=8;

SYS_OP_D

--------

FEFEFF

FEFAFF

FDFF

FCFF

FBFF

FAFF

F9FF

F8FF

8 rows selected.

SCOTT@book> select SYS_OP_UNDESCEND('FEFEFF') from dual ;

SY

--

00

SCOTT@book> select SYS_OP_UNDESCEND('FEFDFF') from dual ;

SYS_

----

0000

SCOTT@book> select SYS_OP_UNDESCEND('FEFCFF') from dual ;

SYS_

----

0001

SCOTT@book> select SYS_OP_UNDESCEND('FEFAFF') from dual ;

SY

--

01

--//這樣大家明白為什麼chr(1)是'0xFEFA'吧,大家自己細細體會吧,不再寫出來了,真心不好寫.

總結:

--//真心佩服oracle的一些設計細節,真是博大精深.

[20181123]關於降序索引問題.txt

相關文章