[20231024]NULL值在索引的情况.txt

[20231024]NULL值在索引的情况.txt --//如果全部键值是NULL,oracle不保存该键值在索引中.如果表后面几个字段是NULL值,oracle不会浪费空间保存相关信息. --//因为在数据块中存在一个保存字段数量的标识，而索引块不存在这个标识。这样对于索引字段最后几个字段为NULL的情况，oracle是 --//如何操作的呢? 如果索引尾部几个字段是NULL呢?以前没有关注这个问题,测试看看. 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> create table empx as select * from emp; Table created. SCOTT@test01p> create unique index pk_empx on empx (empno,comm); Index created. SCOTT@test01p> select * from empx order by 1;      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20       7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~             7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30       7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20       7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30       7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30       7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10       7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10       7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30       7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20       7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30       7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20       7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10 14 rows selected. --//我按照empno排序，这样kd_off[2]指向的就是empno的最小键值。 SCOTT@test01p> @ seg2 pk_empx SCOTT@test01p> @ pr ============================== SEG_MB                        : 0 SEG_OWNER                     : SCOTT SEG_SEGMENT_NAME              : PK_EMPX SEG_PARTITION_NAME            : SEG_SEGMENT_TYPE              : INDEX SEG_TABLESPACE_NAME           : USERS BLOCKS                        : 8 HDRFIL                        : 11 HDRBLK                        : 410 PL/SQL procedure successfully completed. --//dba =11,411 就是索引的root,empx表仅仅14条记录,该地址也同时是叶子节点. 3.bbed观察: BBED> set dba 11,412         DBA             0x02c0019c (46137756 11,412) --//windwos bbed版本 块偏移+1 ,因为无法识别12c的数据文件OS头. BBED> map  File: D:\APP\ORACLE\ORADATA\TEST\TEST01P (11)  Block: 412                                   Dba:0x02c0019c ------------------------------------------------------------  KTB Data Block (Index Leaf)  struct kcbh, 20 bytes                      @0  struct ktbbh, 72 bytes                     @20  struct kdxle, 32 bytes                     @100  b2 kd_off[14]                              @132  ub1 freespace[7784]                        @160  ub1 rowdata[188]                           @7944  ub4 tailchk                                @8188 BBED> map  File: D:\APP\ORACLE\ORADATA\TEST\TEST01P (11)  Block: 412                                   Dba:0x02c0019c ------------------------------------------------------------  KTB Data Block (Index Leaf)  struct kcbh, 20 bytes                      @0  struct ktbbh, 72 bytes                     @20  struct kdxle, 32 bytes                     @100  b2 kd_off[14]                              @132  ub1 freespace[7805]                        @160  ub1 rowdata[167]                           @7965  ub4 tailchk                                @8188 BBED> p kdxle struct kdxle, 32 bytes                      @100    struct kdxlexco, 16 bytes                @100       ub1 kdxcolev                          @100      0x00       ub1 kdxcolok                          @101      0x00       ub1 kdxcoopc                          @102      0x80       ub1 kdxconco                          @103      0x02       --//索引包含2个字段.如果上面建立的索引非唯 一，这里是3，                                                                  --//包括rowid相关信息作为索引的一部分       ub4 kdxcosdc                          @104      0x00000000       sb2 kdxconro                          @108      14         --//14条记录.       b2 kdxcofbo                           @110      64       b2 kdxcofeo                           @112      7848       b2 kdxcoavs                           @114      7784    b2 kdxlespl                              @116      0    sb2 kdxlende                             @118      0    ub4 kdxlenxt                             @120      0x00000000    ub4 kdxleprv                             @124      0x00000000    ub1 kdxledsz                             @128      0x06      --//索引的rowid在前面,没有长度指示器,占6字节.                                                                 --//如果是0，非唯 一索引。是10是全局唯 一索引。                                                                 --//是8是cluster index。    ub1 kdxleunuse                           @129      0x00 BBED> p kd_off b2 kd_off[0]                                @132      8036     --//8036+100(kdxle的偏移)=8136 表示索引的结尾，中间还保留                                                                --//8188-8136 = 52,不知道什么用处。 b2 kd_off[1]                                @134      0        --//kd_off[0],kd_off[1]执行的偏移不对，我估计oracle改变了                                                                --//kdxle结构，这4个字节的内容属于kdxle。 b2 kd_off[2]                                @136      8023 b2 kd_off[3]                                @138      8008 b2 kd_off[4]                                @140      7993 b2 kd_off[5]                                @142      7980 b2 kd_off[6]                                @144      7965 b2 kd_off[7]                                @146      7952 b2 kd_off[8]                                @148      7939 b2 kd_off[9]                                @150      7926 b2 kd_off[10]                               @152      7913 b2 kd_off[11]                               @154      7899 b2 kd_off[12]                               @156      7886 b2 kd_off[13]                               @158      7874 --//以前提过kd_off指向的键值是按照键值排序的.注意bbed的版本有问题,实际上从kd_off[2]开始.     BBED> x /rnnn *kd_off[2] rowdata[179]                                @8123 ------------ flag@8123:     0x00 (NONE) lock@8124:     0x00 keydata[6]:    0x02  0xc0  0x01  0x93  0x00  0x00 data key: col    0[3] @8132: 7369 col    1[0] @8136: *NULL* --//8123+1+1+6+1+3+1 = 8136 --//8188-8136 = 52,确实保留52字节在数据块尾部,为什么? BBED> x /rnnn *kd_off[3] rowdata[164]                                @8108 ------------ flag@8108:     0x00 (NONE) lock@8109:     0x00 keydata[6]:    0x02  0xc0  0x01  0x93  0x00  0x01 data key: col    0[3] @8117: 7499 col    1[2] @8121: 300 --//选择16进制看看. BBED> x /rxxx *kd_off[2] rowdata[179]                                @8123 ------------ flag@8123:     0x00 (NONE) lock@8124:     0x00 keydata[6]:    0x02  0xc0  0x01  0x93  0x00  0x00 data key: col    0[3] @8132:  0xc2  0x4a  0x46 col    1[0] @8136: *NULL* BBED> p tailchk ub4 tailchk                                 @8188     0xb5370601 BBED> dump /v offset 8123 count 100  File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)  Block: 412     Offsets: 8123 to 8191  Dba:0x02c0019c -------------------------------------------------------  000002c0 01930000 03c24a46 ff000000 l ...?....翵F....  00000000 00000000 00000000 00000000 l ................  00000000 00000000 00000000 00000000 l ................  00000000 00000000 00000000 00000000 l ................  00010637 b5                         l ...7  <16 bytes per line> BBED> dump /v offset 8123 count 13  File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)  Block: 412     Offsets: 8123 to 8135  Dba:0x02c0019c -------------------------------------------------------  000002c0 01930000 03c24a46 ff       l ...?....翵F.  <16 bytes per line> --//可以发现NULL保存的信息0xff,前面没有长度指示器。有点奇怪为什么索引在底部保留一段没有保存信息,占用52字节. BBED> x /rxxx *kd_off[5] rowdata[136]                                @8080 ------------ flag@8080:     0x00 (NONE) lock@8081:     0x00 keydata[6]:    0x02  0xc0  0x01  0x93  0x00  0x03 data key: col    0[3] @8089:  0xc2  0x4c  0x43 col    1[0] @8093: *NULL* BBED> dump /v offset 8080 count 13  File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)  Block: 412     Offsets: 8080 to 8092  Dba:0x02c0019c -------------------------------------------------------  000002c0 01930003 03c24c43 ff       l ...?....翷C. <16 bytes per line> --//也就是索引是保留NULL值的,即使在索引字段的尾部。