Creating indexes on big table takes long time. What is even more time consuming is that you get ORA-01654: unable to extend index error while creating indexes. You have to give a emergency call to the storage guys to ask them allocating more space for you. Sometimes you have wait for hours(luckily) or days before extra space is available…and then run the create index statement again...
Therefore, capacity planning becomes essential when you are planning to create indexes. In Oracle 10g or later, there is a procedure called CREATE_INDEX_COST in DBMS_SPACE package which can help you estimate the size of the index from your create indexes
Here is an example:
--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(*)
----------
13405184
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('TEST','T');
PL/SQL procedure successfully completed.
SQL> variable used_bytes number
SQL> variable allocated_bytes number
SQL> set autoprint on
SQL>
SQL> exec dbms_space.create_index_cost( 'create index t_idx on t(object_id,object_name,object_type)', :used_bytes, :allocated_bytes );
PL/SQL procedure successfully completed.
ALLOCATED_BYTES
---------------
645922816
USED_BYTES
----------
428965888
Verification
SQL> create index t_idx on t(object_id,object_name,object_type);
Index created.
select segment_name ,sum(bytes) from dba_segments where owner='TEST' and segment_name='T_IDX' group by segment_name;
SEGMENT_NAME SUM(BYTES)
--------------------------------------------------------------------------------- ----------
T_IDX 637534208
1 row selected.
We see that the CREATE_INDEX_COST says that the estimated index size is 616MB and the actual size of the index is 608MB.
Conclusions
We are happy that this procedure produce accurate result for our simple b-tree index. However, I doubt that if procedure can procedure good result for other types of index such as bitmap or function-based index. I tried other types of indexes, but I found I cannot get a sensible from this procedure, my best guess is since this procedure produces result by the computation of average column length and the columns appeared in the create index statement…Anyways, I believe CREATE_INDEX_COST is good enough for the rough estimation of index size ;-p
Filed under:
Oracle
Post a Comment