该问题也源于某位网友的提问，其实这个问题也非常的现实，有用，具体的问题是：
我们在为某个表创建索引之前，如何估算已将创建的索引需要占据多少空间？下面看我的测试.
SQL> create table t as select * from dba_objects;
Table created.
SQL> begin
2 for i in 1..1000 loop
3 insert /*+ append */into t select * from t;
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at line 3
SQL> begin
2 for i in 1..1000 loop
3 insert /*+ append */into t select * from t;
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at line 3
SQL> select count(*) from t;
COUNT(*)
----------
1629312
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select bytes/1024/1024 from dba_segments where segment_name='T';
BYTES/1024/1024
---------------
272
SQL> create index t_idx_id on t(object_id);
Index created.
SQL> analyze index t_idx_id compute statistics;
Index analyzed.
SQL> select bytes/1024/1024 from dba_segments where segment_name='T_IDX_ID';
BYTES/1024/1024
---------------
29
SQL> select file_id,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024/1024
2 from dba_extents
3 where segment_name='T_IDX_ID';
FILE_ID SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES/1024/1024
---------- -------------------- ---------- ---------- ---------------
5 T_IDX_ID 1 457 .0625
5 T_IDX_ID 3 465 .0625
5 T_IDX_ID 5 473 .0625
5 T_IDX_ID 7 489 .0625
5 T_IDX_ID 9 497 .0625
5 T_IDX_ID 11 505 .0625
5 T_IDX_ID 13 513 .0625
5 T_IDX_ID 15 1161 .0625
5 T_IDX_ID 17 14473 1
5 T_IDX_ID 19 14601 1
5 T_IDX_ID 21 14729 1
5 T_IDX_ID 23 27017 1
5 T_IDX_ID 25 27145 1
5 T_IDX_ID 27 27273 1
5 T_IDX_ID 29 27401 1
5 T_IDX_ID 31 27529 1
5 T_IDX_ID 33 28425 1
5 T_IDX_ID 35 28553 1
5 T_IDX_ID 37 28681 1
5 T_IDX_ID 39 33033 1
5 T_IDX_ID 41 33161 1
5 T_IDX_ID 43 33289 1
6 T_IDX_ID 0 593 .0625
6 T_IDX_ID 2 601 .0625
6 T_IDX_ID 4 609 .0625
6 T_IDX_ID 6 617 .0625
6 T_IDX_ID 8 625 .0625
6 T_IDX_ID 10 633 .0625
6 T_IDX_ID 12 641 .0625
6 T_IDX_ID 14 9609 .0625
6 T_IDX_ID 16 9353 1
6 T_IDX_ID 18 9481 1
6 T_IDX_ID 20 29705 1
6 T_IDX_ID 22 29833 1
6 T_IDX_ID 24 30729 1
6 T_IDX_ID 26 30857 1
6 T_IDX_ID 28 30985 1
6 T_IDX_ID 30 31113 1
6 T_IDX_ID 32 33417 1
6 T_IDX_ID 34 33545 1
6 T_IDX_ID 36 33673 1
6 T_IDX_ID 38 33801 1
6 T_IDX_ID 40 33929 1
6 T_IDX_ID 42 34057 1
44 rows selected.
++++++ index block 结构 ++++++
BBED> set file 5 block 457
FILE# 5
BLOCK# 457
BBED> map /v
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 457 Dba:0x014001c9
------------------------------------------------------------
KTB Data Block (Index Leaf)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
b2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44
struct kdxle, 32 bytes @100
struct kdxlexco, 16 bytes @100
b2 kdxlespl @116
sb2 kdxlende @118
ub4 kdxlenxt @120
ub4 kdxleprv @124
ub1 kdxledsz @128
ub1 kdxleunuse @129
b2 kd_off[512] @132
ub1 freespace[828] @1156
ub1 rowdata[6144] @1984
ub4 tailchk @8188
SQL> select table_name,column_name,AVG_COL_LEN
2 from user_tab_columns
3 where table_name='T';
TABLE_NAME COLUMN_NAME AVG_COL_LEN
---------- ------------------------------ -----------
T OWNER 5
T OBJECT_NAME 24
T SUBOBJECT_NAME 2
T OBJECT_ID 4
T DATA_OBJECT_ID 2
T OBJECT_TYPE 8
T CREATED 7
T LAST_DDL_TIME 7
T TIMESTAMP 19
T STATUS 5
T TEMPORARY 1
T GENERATED 1
T SECONDARY 1
13 rows selected.
SQL> select dbms_metadata.get_ddl('TABLE','T','ROGER') from dual;
DBMS_METADATA.GET_DDL('TABLE','T','ROGER')
--------------------------------------------------------------------------------
CREATE TABLE "ROGER"."T"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ROGER"
从bbed的输出信息我们可以清楚的了解到index block的结构，这里我们并不需要
了解每个机构的具体含义，有个整理的轮廓就行了，我们可以这样想：
index block也是数据块，假如我们把10w条的信息存到索引block中，要想知道索引的大小，
那么我们就要知道一共占据了多少个index block？再深入一点，那就是如果我们知道了每个
index block所能存放的数据条数不就行了吗？
总数据条数 / 每个index block所容纳的数据条数 = index block总数
这里我们开始进行计算：
假如 每个index block最大能容纳Y条:
block size(8192) = kcbh + ktbbh + kdxle + kd_off + DATA + block_size * Pctfree + 4
= 20 + 72 + 32 + 2 * Y + Y * ( 4 + rowid ) + 8192 * 0.1 + 4
8192 = 92 + 32 + 16y + 819.2 + 4
SQL> select (8192-819.2-92-32-4)/16 from dual;
(8192-819.2-92-32-4)/16
-----------------------
452.8
这里计算出来的结果是，如果我们在object_id列上创建index的话，单个index block能容纳453.05条信息。
那么我们需要多少个 index block 呢？ 很简单，如下：
SQL> select (1629312/452.8) from dual;
(1629312/452.8)
---------------
3598.30389
到这里，我们计算即将创建的index的大小是：
SQL> select (1629312/452.8)*8192/1024/1024 from dual;
(1629312/452.8)*8192/1024/1024
------------------------------
28.1117491
从上面的计算结果来看，应该是29m大小。跟我们前面的查询结果完全一致。
最后还有个问题，我们这里是针对单列index而言，如果是复合index呢？
SQL> select table_name,column_name,AVG_COL_LEN
2 from user_tab_columns
3 where table_name='T';
TABLE_NAME COLUMN_NAME AVG_COL_LEN
---------- ------------------------------ -----------
T OWNER 5
T OBJECT_NAME 24
T SUBOBJECT_NAME 2
T OBJECT_ID 4
T DATA_OBJECT_ID 2
T OBJECT_TYPE 8
T CREATED 7
T LAST_DDL_TIME 7
T TIMESTAMP 19
T STATUS 5
T TEMPORARY 1
T GENERATED 1
T SECONDARY 1
假如我们要给(owner,object_id) 创建一个复合index，那么即将创建的index的大小是多少呢？
这里我假设每个index block此时最大能容纳Y条信息：
对于8k的block：
block_size(8192)= = kcbh + ktbbh + kdxle + kd_off + DATA + block_size * Pctfree + 4
= 20 + 72 + 32 + 2 * Y + Y * ( 4 + 5 + rowid ) + 8192 * 0.1 + 4
8192 = 92 + 32 + 21Y + 819.2 + 4
SQL> select (8192-92-32-819.2-4)/21 from dual;
(8192-92-32-819.2-4)/21
-----------------------
344.990476
此时 Y = 344.990476
那么新创建的复合index为多大呢？
SQL> select 1629312/((8192-92-32-819.2-4)/21) *8192 /1024/1024 from dual;
1629312/((8192-92-32-819.2-4)/21)*8192/1024/1024
------------------------------------------------
36.8966707
大概估算为37M左右。
我们来看看实际情况如何：
SQL> conn roger/roger
Connected.
SQL> create index t_idx_owner_id on t(owner,object_id);
Index created.
SQL> analyze index t_idx_owner_id compute statistics;
Index analyzed.
SQL> select bytes/1024/1024 from dba_segments where segment_name='T_IDX_OWNER_ID';
BYTES/1024/1024
---------------
39
这里需要说明一下的是，我这里是以leaf block为计算，因为还涉及到branch block，
其结构是不同的，所以最终的结果有微小的差异，不过我想这已经实现我们的目的了吧！
下面分别是index branch block 和 leaf block的结构：
BBED> map /v
File: /home/ora10g/oradata/roger/roger02.dbf (6)
Block: 28671 Dba:0x01806fff
------------------------------------------------------------
KTB Data Block (Index Leaf)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
b2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44
struct kdxle, 32 bytes @100
struct kdxlexco, 16 bytes @100
b2 kdxlespl @116
sb2 kdxlende @118
ub4 kdxlenxt @120
ub4 kdxleprv @124
ub1 kdxledsz @128
ub1 kdxleunuse @129
b2 kd_off[336] @132
ub1 freespace[836] @804
ub1 rowdata[6488] @1640
ub4 tailchk @8188
BBED> set file 6 block 34700
FILE# 6
BLOCK# 34700
BBED> map /v
File: /home/ora10g/oradata/roger/roger02.dbf (6)
Block: 34700 Dba:0x0180878c
------------------------------------------------------------
KTB Data Block (Index Branch)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 48 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
b2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[1], 24 bytes @44
struct kdxbr, 24 bytes @76
struct kdxbrxco, 16 bytes @76
ub4 kdxbrlmc @92
sb2 kdxbrsno @96
b2 kd_off[368] @100
ub1 freespace[15] @836
ub1 rowdata[7277] @851
ub4 tailchk @8188
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
