> 主要分析索引內容，確定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</block_id></file_id>

【NUMBER】Oracle資料庫最佳化之理解NUMBER儲存機制

相關文章