【NUMBER】Oracle数据库优化之理解NUMBER存储机制

主要分析索引内容，确定number存储机制，0和1的区别。 2023.1.18 --by firsouler

环境准备

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的复合索引占用空间更小

--查看表和索引大小
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 内容

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内容

--通过上述方式，我们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