创建index之前如何确定其大小（采用了dbms_space.create_index_cost包收藏于vmcd）
看了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.
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
= 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去计算 还是会出现一定的偏差
