该问题也源于某位网友的提问，其实这个问题也非常的现实，有用，具体的问题是： 我们在为某个表创建索引之前，如何估算已将创建的索引需要占据多少空间？下面看我的测试.
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 #map /v 是一个bbed的命令，通过这命令可以清楚的看到index leaf block的结构。 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 |
