What character does TERM use in [20190910] index branching block. TXT

Time:2019-10-9

What character does TERM use in [20190910] index branching block. TXT

// Do index block dump, some root, branch node TERM, never pay attention to the use of character representation, a simple look.

1. Environment:
[email protected]> @ 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

[email protected]> create table t as select to_char(rownum,’FM’||lpad(‘0′,20,’0’)) v1 from dual connect by level<=2000;
Table created.

[email protected]> create index i_t_v1 on t(v1);
Index created.

[email protected]> select header_file,header_block from dba_segments where owner=user and segment_name=’I_T_V1′;
HEADER_FILE HEADER_BLOCK
———– ————
         11          506

[email protected]> @ treedump i_t_v1
old   1: select object_id from user_objects where object_name = upper(‘&&1’) and object_type = ‘INDEX’
new   1: select object_id from user_objects where object_name = upper(‘i_t_v1’) and object_type = ‘INDEX’
 OBJECT_ID
———-
     27931

old   1: alter session set events ‘immediate trace name treedump level &m_index_id’
new   1: alter session set events ‘immediate trace name treedump level      27931’
Session altered.        

2. Check dump:
–//dump content:
—– begin tree dump
branch: 0x2c001fb 46137851 (0: nrow: 9, level: 1)

*** 2019-09-10T20:55:45.660043+08:00 (TEST01P(3))
   leaf: 0x2c001fc 46137852 (-1: row:224.224 avs:832)
   leaf: 0x2c001fd 46137853 (0: row:224.224 avs:832)
   leaf: 0x2c001fe 46137854 (1: row:224.224 avs:832)
   leaf: 0x2c001ff 46137855 (2: row:224.224 avs:832)
   leaf: 0x2c003e0 46138336 (3: row:224.224 avs:832)
   leaf: 0x2c003e1 46138337 (4: row:224.224 avs:832)
   leaf: 0x2c003e2 46138338 (5: row:224.224 avs:832)
   leaf: 0x2c003e3 46138339 (6: row:224.224 avs:832)
   leaf: 0x2c003e4 46138340 (7: row:208.208 avs:1344)
—– end tree dump

–//0x2c001fb  = set dba 11,507 = alter system dump datafile 11 block 507
–// Dump root node.

[email protected]> alter system checkpoint ;
System altered.

[email protected]> alter system dump datafile 11 block 507;
System altered.

–//dump content:
Block header dump:  0x02c001fb
 Object id on Block? Y
 seg/obj: 0x6d1b  csc:  0x0000000000a2b4d9  itc: 1  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x2c001f8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn  0x0000000000a2b4d9
Branch block dump
=================
header address 629538892=0x2586004c
kdxcolev 1
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 8
kdxcofbo 44=0x2c
kdxcofeo 7852=0x1eac
kdxcoavs 7808
kdxbrlmc 46137852=0x2c001fc
kdxbrsno 0
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8034] dba: 46137853=0x2c001fd
col 0; len 20; (20):  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 32 32 35
–//30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 32 32 35 = 00000000000000000225
col 1; TERM
–// TERM appears.
row#1[8008] dba: 46137854=0x2c001fe
col 0; len 20; (20):  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 34 34 39
col 1; TERM
row#2[7982] dba: 46137855=0x2c001ff
col 0; len 20; (20):  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 36 37 33
col 1; TERM
row#3[7956] dba: 46138336=0x2c003e0
col 0; len 20; (20):  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 38 39 37
col 1; TERM
row#4[7930] dba: 46138337=0x2c003e1
col 0; len 20; (20):  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 31 31 32 31
col 1; TERM
row#5[7904] dba: 46138338=0x2c003e2
col 0; len 20; (20):  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 31 33 34 35
col 1; TERM
row#6[7878] dba: 46138339=0x2c003e3
col 0; len 20; (20):  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 31 35 36 39
col 1; TERM
row#7[7852] dba: 46138340=0x2c003e4
col 0; len 20; (20):  30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 31 37 39 33
col 1; TERM
—– end of branch block dump —–
End dump data blocks tsn: 4 file#: 11 minblk 507 maxblk 507

TERM can be found in // dump, indicating termination, that is, leaf nodes do not need to save the full key value, only part of it is ok.
The corresponding coding of TERM can’t be seen here, of course. Look at the front of the dump file as follows:

Dump of memory from 0x0000000025860000 to 0x0000000025862000
025860000 0000A206 02C001FB 00A2B4DD 04010000  […………….]
025860010 0000AFCD 00000002 00006D1B 00A2B4D9  [………m……]
025860020 00008000 00320001 02C001F8 0000FFFF  [……2………]
025860030 00000000 00000000 00000000 80008000  […………….]
025860040 00A2B4D9 00000000 00000000 02800001  […………….]
025860050 00000000 002C0008 1E801EAC 02C001FC  [……,………]
025860060 00000000 00001F7C 1F481F62 1F141F2E  [….|…b.H…..]
025860070 1EE01EFA 1EAC1EC6 00000000 00000000  […………….]
025860080 00000000 00000000 00000000 00000000  […………….]
        Repeat 486 times
025861EF0 00000000 00000000 02C003E4 30303014  [………….000]
025861F00 30303030 30303030 30303030 39373130  [0000000000000179]
025861F10 03E3FE33 301402C0 30303030 30303030  [3……000000000]
025861F20 30303030 31303030 FE393635 02C003E2  [00000001569…..]
025861F30 30303014 30303030 30303030 30303030  [.000000000000000]
025861F40 34333130 03E1FE35 301402C0 30303030  [01345……00000]
025861F50 30303030 30303030 31303030 FE313231  [000000000001121.]
025861F60 02C003E0 30303014 30303030 30303030  […..00000000000]
025861F70 30303030 39383030 01FFFE37 301402C0  [000000897……0]
025861F80 30303030 30303030 30303030 30303030  [0000000000000000]
025861F90 FE333736 02C001FE 30303014 30303030  [673……0000000]
025861FA0 30303030 30303030 34343030 01FDFE39  [0000000000449…]
025861FB0 301402C0 30303030 30303030 30303030  […0000000000000]
025861FC0 30303030 FE353232 00000000 00000000  [0000225………]
                   ~~~~~~~~
025861FD0 00000000 00000000 00000000 00000000  […………….]
        Repeat 1 times
025861FF0 00000000 00000000 00000000 B4DD0601  […………….]

–// Look at the underlined content and you can see that the term corresponding code is 0xfe.
Of course, my example is special. If the index above is unique, this would not happen, because ROWID is in front of the index key value.

3. bbed look at:
BBED> set dba 11,508
        DBA             0x02c001fc (46137852 11,508)
–// Note: The offset of bbed block under windows is + 1.

BBED> p kd_off
b2 kd_off[0]    @100      8060
b2 kd_off[1]    @102      0
b2 kd_off[2]    @104      8034
b2 kd_off[3]    @106      8008
b2 kd_off[4]    @108      7982
b2 kd_off[5]    @110      7956
b2 kd_off[6]    @112      7930
b2 kd_off[7]    @114      7904
The index structure has some problems, kd_off [0], kd_off [1] pointed to the wrong offset. In fact, starting from kd_off [2].

BBED> x /rcx *kd_off[2]
rowdata[186]                                @8110
————
child dba:     0x02c001fd
separator key:
col   0[20] @8115: 00000000000000000225
col    1[0] @8136: *TERM*

BBED> x /rcx *kd_off[3]
rowdata[160]                                @8084
————
child dba:     0x02c001fe
separator key:
col   0[20] @8089: 00000000000000000449
col    1[0] @8110: *TERM*
Migration 8110 is the beginning of the next record. It’s estimated to be a bug in bbed.

BBED> dump /v offset 8110
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
 Block: 508                               Offsets: 8110 to 8191                            Dba:0x02c001fc
———————————————————————————————————–
 fd01c002 14303030 30303030 30303030 30303030 30303232 35fe0000 00000000 l ??.00000000000000000225?…..
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l …………………………..
000 000 000 000 000 000 000 000 000 000 000 106 ddb4
 <32 bytes per line>

BBED> x /rcx offset 8110
rowdata[186]                                @8110
————
child dba:     0x02c001fd
separator key:
col   0[20] @8115: 00000000000000000225
col    1[0] @8136: *TERM*

BBED> dump /v offset 8109 count 2
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
 Block: 508                               Offsets: 8109 to 8110                            Dba:0x02c001fc
———————————————————————————————————–
 fefd                                                                    l 
 <32 bytes per line>

The x command shown by //bbed shows that col 1 has a problem with offset. In fact, column length is 0. offset 8110 is another record.
It can be found that TERM actually corresponds to ASCII code 0 xfe. This is a good way to analyze the problems I have encountered before. Links:
–//http://blog.itpub.net/267265/viewspace-1291526/=> [20141008] Index string length problem.txt

–// For the length of the index string:
When the length of a string is less than or equal to 127, one byte is used to represent the length.
When the string is greater than or equal to 128, use 2 bytes to save the length, the content is string length + 0x8000.
I don’t understand why Oracle has to create two different ways to save strings, unlike data blocks.

// At that time, I didn’t understand why Oracle had to create two different ways to save strings. Now I understand.

–// I wrote an article on how to save VARCHAR2 (4000). The links are as follows:
How to save //http://blog.itpub.net/267265/viewspace-2148818/ => [20171218] VARCHAR2 (4000). TXT

If a row can be stored in a data block, its row header will need no less than 3 bytes of capacity. Store the header information in turn
–// is the column length and column value of each column. Column length is stored before column value. If column value does not exceed 250 bytes, Oracle uses 1 byte to store it.
–// Column length; if the column value exceeds 250 bytes, use 3 bytes to store its column length. The storage space required for column data depends on the data type of this column. as
If the data type of a column is variable length, the space required to store the column value may grow or shrink as the data is updated.

// Summary at that time:
If the column value length is less than or equal to 250 bytes, Oracle uses 1 byte to store its column length. The content is the length of the field.
–//2. If the column value length exceeds 250 bytes, use 3 bytes to store its column length. The first byte uses 0xFE (more than 250), and the last two bytes represent column value length.

// / Obviously part of the string length indicator of 0xFE in the data block, used to indicate that the saved character exceeds 250 bytes. The index TERM is represented by 0xfe.
If the length of the index field string is greater than 250, the key length can no longer be saved in a similar way in the data block. In this way, oraclea must adopt a new schema to define the string length in the index.
–// Language is not easy to express, or through examples to illustrate:

4. Continue testing:
create table t1 (v1 varchar2(4000));
insert into t1 values (lpad(‘1′,127,’1’));
insert into t1 values (lpad(‘2′,128,’2’));
insert into t1 values (lpad(‘3′,4000,’3’));
commit ;
create index i_t1_v1 on t1(v1);
alter system checkpoint ;

[email protected]> select header_file,header_block from dba_segments where owner=user and segment_name=’I_T1_V1′;
HEADER_FILE HEADER_BLOCK
———– ————
         11          410

The root node of the index is 11,411. Observed by bbed:

BBED> set dba 11,412
        DBA             0x02c0019c (46137756 11,412)

BBED> p kd_off
b2 kd_off[0]  @132      8036
b2 kd_off[1]  @134      0
b2 kd_off[2]  @136      7899

BBED> x /rcx *kd_off[2]
rowdata[4154]                               @7999
————-
[email protected]:     0x00 (NONE)
[email protected]:     0x00
data key:
col  0[127] @8002: 11111111…1111111111
col    1[6] @8130:  0x02  0xc0  0x01  0x95  0x00  0x00

BBED> dump /v offset 8001 count 10
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
 Block: 412                               Offsets: 8001 to 8010                            Dba:0x02c0019c
———————————————————————————————————–
 7f313131 31313131 3131                                                  l .111111111
<32 bytes per line>

–//7f = 127, using one byte to represent the length of the string.

BBED> dump /v offset 138 count 4
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
 Block: 412                               Offsets:  138 to  141                            Dba:0x02c0019c
———————————————————————————————————–
 501ea50e                                                                l P.?
<32 bytes per line>

–// Byte inversion order 0x1e50 = 7760, 0x0ea5 = 3749. Relative offset is 7760, 3749. Looking at the previous kd_off [2] offset, we can see that absolute offset needs to be increased by 100.

BBED> x /rcx offset 7860
rowdata[4015]                               @7860
————-
[email protected]:     0x00 (NONE)
[email protected]:     0x00
data key:
col  0[128] @7864: 22222…22222
col    1[6] @7993:  0x02  0xc0  0x01  0x95  0x00  0x01

BBED> dump /v offset 7862 count 10
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
 Block: 412                               Offsets: 7862 to 7871                            Dba:0x02c0019c
———————————————————————————————————–
 80803232 32323232 3232                                                  l ..22222222
<32 bytes per line>
–// Two times 0 x 80.

BBED> x /rcx offset 3849
rowdata[4]                                  @3849
———-
[email protected]:     0x00 (NONE)
[email protected]:     0x00
data key:
col 0[4000] @3853: 3333…………
……..3333333
col    1[6] @7854:  0x02  0xc0  0x01  0x95  0x00  0x02

BBED> dump /v offset 3851 count 10
 File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)
 Block: 412                               Offsets: 3851 to 3860                            Dba:0x02c0019c
———————————————————————————————————–
 8fa03333 33333333 3333                                                  l .?3333333
 <32 bytes per line>

–//0x8fa0 -0x8000 = 0xfa0 = 4000.

–// For the length of the index string:
When the length of a string is less than or equal to 127, one byte is used to represent the length.
When the string is greater than or equal to 128, use 2 bytes to save the length, the content is string length + 0x8000.

Before learning oracle, it was hard to understand why the length of a string in a data block is less than or equal to 250 bytes. Oracle uses 1 byte to store its column length. The content is the length of a field.
Why define the boundary at 250.0xff to hold null values, 0xFE as part of the coding of the > 250 string length indicator (TERM in the index).
Oracle has reserved 0xfb, 0xfc, 0xfd for this reason. I don’t know where I will use it.