> 主要分析索引内容，确定number存储机制， 2023.1.18 --by firsouler ### 环境准备 ``` sql create table t1 as select * from dba_objects; insert into t1 select * from t1; --执行5次 commit; create table t2 as select * from t1; update t1 set object_name=null where object_id<7000; commit; update t2 set object_name=null where object_id<7000; commit; create index ind_t1_name on t1(object_name,0); create index ind_t2_name on t2(object_name,1); exec dbms_stats.gather_table_stats('MYTEST','T1',degree => 4,cascade => true,method_opt=>'for all columns size auto',estimate_percent=>100); exec dbms_stats.gather_table_stats('MYTEST','T2',degree => 4,cascade => true,method_opt=>'for all columns size auto',estimate_percent=>100); ``` 通过检查索引大小及占用块数，我们发现，常数0的复合索引占用空间更小 ``` sql --查看表和索引大小 col segment_name for a20 select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name in('T1','T2','IND_T1_NAME','IND_T2_NAME'); SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 -------------------- ------------------ --------------- IND_T1_NAME INDEX 120 IND_T2_NAME INDEX 128 T1 TABLE 352 T2 TABLE 352 col index_name for a20 col table_name for a10 col LEAF_BLOCKS for 99999999 col NUM_ROWS for 99999999 select index_name,TABLE_NAME,LEAF_BLOCKS,NUM_ROWS from user_indexes where index_name in ('IND_T1_NAME','IND_T2_NAME'); INDEX_NAME TABLE_NAME LEAF_BLOCKS NUM_ROWS -------------------- ---------- ----------- --------- IND_T1_NAME T1 15004 2310176 IND_T2_NAME T2 15328 2310176 ``` ### 查看IND_T1_NAME 内容 ``` sql col object_name for a16 select object_id,object_name,object_type from user_objects where object_name in ('IND_T1_NAME','IND_T2_NAME'); OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ---------------- ----------------------- 242122 IND_T1_NAME INDEX 242124 IND_T2_NAME INDEX ALTER SESSION SET EVENTS 'immediate trace name treedump level 242122'; ----- begin tree dump branch: 0x2816183 42033539 (0: nrow: 98, level: 2) branch: 0x281aa74 42052212 (-1: nrow: 230, level: 1) leaf: 0x2816184 42033540 (-1: row:254.254 avs:820) leaf: 0x2816185 42033541 (0: row:199.199 avs:819) ...... leaf: 0x241dd88 37870984 (229: row:512.512 avs:828) leaf: 0x241dd89 37870985 (230: row:512.512 avs:828) select DBMS_UTILITY.data_block_address_file(37870984) RELATIVE_FNO,DBMS_UTILITY.data_block_address_block(37870984) blk_id from dual; --注意,上述转换是相对文件号，需要转换成绝对文件号file_id. select RELATIVE_FNO,FILE_ID from dba_data_files where RELATIVE_FNO=9; alter system dump datafile <file_id> block <block_id>; --IND_T1_NAME ,我们可以看到，索引有列，复合索引两列，第一列是空值，第二列是80，也就是我们写的0，第三列是rowid。 Block header dump: 0x0241dd88 Object id on Block? Y seg/obj: 0x3b1ca csc: 0x000000000c43aa16 itc: 2 flg: E typ: 2 - INDEX brn: 0 bdba: 0x241da03 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 0x000000000c43aa16 Leaf block dump =============== header address 3595444324=0xd64e2064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 3 kdxcosdc 0 kdxconro 512 kdxcofbo 1060=0x424 kdxcofeo 1888=0x760 kdxcoavs 828 kdxlespl 0 kdxlende 0 kdxlenxt 37870985=0x241dd89 kdxleprv 37870983=0x241dd87 kdxledsz 0 kdxlebksz 8032 row#0[8020] flag: -------, lock: 0, len=12 col 0; NULL col 1; len 1; (1): 80 col 2; len 6; (6): 02 81 3e ed 00 0b ``` ### 查看IND_T2_NAME内容 ``` sql --通过上述方式，我们dump 索引IND_T2_NAME select DBMS_UTILITY.data_block_address_file(42066656) RELATIVE_FNO, (select file_id from dba_data_files where relative_fno=DBMS_UTILITY.data_block_address_file(42066656)) file_id, DBMS_UTILITY.data_block_address_block(42066656) blk_id from dual; RELATIVE_FNO FILE_ID BLK_ID ------------ ---------- ---------- 10 15 123616 alter system dump datafile 15 block 123616; --如下所示，最有一行，可看出，IND_T2_NAME 也有三列，空值列，常数1，rowid。 其中常数1占用了两个字节 Block header dump: 0x0281e2e0 Object id on Block? Y seg/obj: 0x3b1cc csc: 0x000000000c43b071 itc: 2 flg: E typ: 2 - INDEX brn: 0 bdba: 0x281e200 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 0x000000000c43b071 Leaf block dump =============== header address 139662300258404=0x7f05a9cba064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 3 kdxcosdc 0 kdxconro 478 kdxcofbo 992=0x3e0 kdxcofeo 1818=0x71a kdxcoavs 826 kdxlespl 0 kdxlende 0 kdxlenxt 42066657=0x281e2e1 kdxleprv 42066655=0x281e2df kdxledsz 0 kdxlebksz 8032 row#0[8019] flag: -------, lock: 0, len=13 col 0; NULL col 1; len 2; (2): c1 02 col 2; len 6; (6): 02 81 a3 32 00 27 ``` ### 总结 通过以上，我们可知，常数0 存储占用一个字节，常数1占用两个字节，这跟Oracle数据库存储number机制有关系。 因为有负数、小数点等，Oracle采用了如下方式表示： ORACLE数据库中存储的number类型包含3个部分: HEAD部分（标记占用了几位）, DATA部分, 符号位. 对正数来说, 符号位省略, 对0来说, 只有80. > Oracle是以十六进制00-FF来表示所有的number, 所以为了编码的对称, 首先将number分为正负, 所以以00-FF的中间位置80, 也就是十进制的128来表示0, HEAD部分小于80,即为负数,大于80即为正数. - 00-3E表示 x <= -1 - 3F-7F 表示 -1< x <0 - 81-C0 表示 0< x < 1 - C1-FF 表示 1<= x Oracle数据库的优化，需要掌握其本身特性，才能更好的发挥它的优势。 ### 参考 - http://www.itpub.net/forum.php?mod=viewthread&tid=308317 - https://www.likecs.com/show-306981395.html