Jul 24

创建index之前如何确定其大小(采用了dbms_space.create_index_cost包收藏于vmcd) 不指定

admin , 22:55 , 数据库 » 数据库(Oracle管理) , 评论(0) , 引用(0) , 阅读(1252) , Via 本站原创 | |
搜索
我已经获得阿里云幸运券,准备分享给您。请点击获取  

创建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.
 
SQLselect 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去计算 还是会出现一定的偏差

Tags: , ,