Oracle中number数据类型的存储机制

bisal的个人杂货铺 2023-01-12

784

Oracle中number数据类型存储的是整型，碰巧看到这篇文章讲解了通过分析索引了解0和1的存储机制，值得学习一下。

P.S. https://www.modb.pro/db/605566

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