昨天看了roger的一篇文章，如何在创建index 之前估算它的大小，这里有一种更简单的方法实现：

主要思路就是采用了dbms_space.create_index_cost这个包

–create a testing table named t
SQL> create table t as select * from dba_objects;

Table created.

SQL> declare
begin
for i in 1..10 loop
insert /*+ APPEND */ into t select * from t;
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

COUNT(*)
———-
38171648

1 row selected.

–As told by Oracle docs, you have to have updated statistics in order to allow CREATE_INDEX_COST to produce accurate estimation
SQL> exec dbms_stats.gather_table_stats(‘LIU’,’T’);

PL/SQL procedure successfully completed.

SQL> variable used_bytes number
SQL> variable allocated_bytes number
SQL> set autoprint on
SQL> exec dbms_space.create_index_cost( ‘create index t_idx on t(object_id,object_name,object_type) tablespace PURCHASE’, :used_bytes, :allocated_bytes );

PL/SQL procedure successfully completed.

ALLOCATED_BYTES
—————
1946157056

USED_BYTES
———-
1259664384

Verification
SQL> create index t_idx on t(object_id,object_name,object_type);

Index created.

SQL> select segment_name ,sum(bytes) from dba_segments where owner=’LIU’ and segment_name=’T_IDX’ group by segment_name;

SEGMENT_NAME SUM(BYTES)
——————————————————————————— ———-
T_IDX 1930428416

1 row selected.

可以看到预计空间为 1946157056byte 实际空间为 1930428416byte 结果极其的接近。

按照roger的方法 我们再计算一次：

对于8k的block：

block_size(8192)= = kcbh + ktbbh + kdxle + kd_off + DATA + block_size * Pctfree + 4
= 20 + 72 + 32 + 2 * Y + Y * ( 20 + 5 + 8 + rowid ) + 8192 * 0.1 + 4
8192 = 92 + 32 + 45Y + 819.2 + 4

SQL> SELECT (8192-92-32-819.2-4)/45 FROM dual;

(8192-92-32-819.2-4)/45
———————–
160.995

SQL> SELECT 38171648/((8192-92-32-819.2-4)/45) *8192 from dual;

38171648/((8192-92-32-819.2-4)/45)*8192
————————————————-
1942249319.354037

可以看到如果单用leaf block计算 同样很接近真实值,当然如果对一个very big的index而言 仅仅用leaf block去计算 还是会出现一定的偏差

Reference:http://www.killdb.com/2012/02/26/how-to-calculate-indexes-size-when-you-create-index.html?wpc=dlc#comment-805