Oracle中number数据类型存储的是整型，碰巧看到这篇文章讲解了通过分析索引了解0和1的存储机制，值得学习一下。
P.S. https://www.modb.pro/db/605566
代码语言：javascript复制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的复合索引占用空间更小，
代码语言：javascript复制--查看表和索引大小
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 2310176IND_T1_NAME内容，
代码语言：javascript复制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 0bIND_T2_NAME内容，
代码语言：javascript复制--通过上述方式，我们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
