创建index之前如何确定其大小
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 创建index之前如何确定其大小
|
1 2 3 |
该问题也源于某位网友的提问，其实这个问题也非常的现实，有用，具体的问题是： 我们在为某个表创建索引之前，如何估算已将创建的索引需要占据多少空间？下面看我的测试. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
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. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
++++++ 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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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" |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
从bbed的输出信息我们可以清楚的了解到index block的结构，这里我们并不需要 了解每个机构的具体含义，有个整理的轮廓就行了，我们可以这样想： index block也是数据块，假如我们把10w条的信息存到索引block中，要想知道索引的大小， 那么我们就要知道一共占据了多少个index block？再深入一点，那就是如果我们知道了每个 index block所能存放的数据条数不就行了吗？ 总数据条数 / 每个index block所容纳的数据条数 = index block总数 这里我们开始进行计算： 假如 每个index block最大能容纳Y条: <span style="color: #0000ff;"> 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 </span> |
|
1 2 3 4 5 |
SQL> select (8192-819.2-92-32-4)/16 from dual; (8192-819.2-92-32-4)/16 ----------------------- 452.8 |
|
1 2 |
这里计算出来的结果是，如果我们在object_id列上创建index的话，单个index block能容纳453.05条信息。 那么我们需要多少个 index block 呢？ 很简单，如下： |
|
1 2 3 4 5 |
SQL> select (1629312/452.8) from dual; (1629312/452.8) --------------- 3598.30389 |
|
1 |
到这里，我们计算即将创建的index的大小是： |
|
1 2 3 4 5 |
SQL> select (1629312/452.8)*8192/1024/1024 from dual; (1629312/452.8)*8192/1024/1024 ------------------------------ 28.1117491 |
|
1 2 3 |
从上面的计算结果来看，应该是29m大小。跟我们前面的查询结果完全一致。 最后还有个问题，我们这里是针对单列index而言，如果是复合index呢？ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 |
|
1 2 3 4 5 6 7 8 9 10 |
假如我们要给(owner,object_id) 创建一个复合index，那么即将创建的index的大小是多少呢？ 这里我假设每个index block此时最大能容纳Y条信息： 对于8k的block： <span style="color: #0000ff;"> 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 </span> |
|
1 2 3 4 5 |
SQL> select (8192-92-32-819.2-4)/21 from dual; (8192-92-32-819.2-4)/21 ----------------------- 344.990476 |
|
1 2 3 |
此时 Y = 344.990476 那么新创建的复合index为多大呢？ |
|
1 2 3 4 5 |
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 |
|
1 2 3 |
大概估算为37M左右。 我们来看看实际情况如何： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 |
|
1 2 3 4 |
这里需要说明一下的是，我这里是以leaf block为计算，因为还涉及到branch block， 其结构是不同的，所以最终的结果有微小的差异，不过我想这已经实现我们的目的了吧！ 下面分别是index branch block 和 leaf block的结构： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
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 |
9 Responses to “创建index之前如何确定其大小”
BBED> set file 5 block 457
FILE# 5
BLOCK# 457
BBED> map /v
请问这里是啥意思？
to xichen-小毛:
map /v 是一个bbed的命令，通过这命令可以清楚的看到index leaf block的结构。
来你博客逛下！
很好，学习了。。。望ROGER先生 能多分享点资料。谢谢。。
to 康杰： 很多资料我已经分享到论坛 http://databasefans.com/index.php?m=bbs 了。
我这里是以leaf block为计算，因为还涉及到branch block，
其结构是不同的，所以最终的结果有微小的差异，不过我想这已经实现我们的目的了吧！
我测试了一下 如果index size 很大 那么最终的结果相差很大
[…] Reference:http://www.killdb.com/2012/02/26/how-to-calculate-indexes-size-when-you-create-index.html?wpc=dlc#co… […]
to VMCD:
你后计算那里有点错误，你已经是3个索引列了。
dbms_create包计算出来也差别不小啊，1856和1841 的差异！
[…] 创建index之前如何确定其大小? 该文原文地址为： 创建index之前如何确定其大小 […]
Leave a Reply
You must be logged in to post a comment.