How to estimate index size

This post is also available at: Português

To estimate the size of an index before creating it, we can use the DBMS_SPACE package. This is useful for assessing the impact of creating a new index or changing the parameters of an existing index.

DBMS_SPACE.CREATE_INDEX_COST

The syntax of a procedure is as follows:

variable used number 
variable alloc number 
col used for 999,999,999,999 
col alloc for 999,999,999,999 
set autoprint on 
exec dbms_space.create_index_cost('', :used, :alloc );

Replace with the command to create the index. The used variable estimates the space needed for the index data. The alloc variable estimates the space that will be allocated in the tablespace.

Points to consider before executing the command:

• The table where the index will be created must already exist.
• The statistics in this table need to be up to date.

Test environment

With the brief introduction above, let’s prepare an environment for the test.

SQL> create table teste as select * from all_objects;

Table created.

SQL> insert into teste (select * from teste);

90129 rows created.

*Repeti por 7 vezes o comando. A tabela ficou com um total de 5768256 linhas.

Having created the table, let’s collect statistics from it.

SQL> 
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,'TESTE',
METHOD_OPT=>'FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS',
CASCADE=>TRUE);
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

Now let’s see what the index size is for the OBJECT_ID column.

exec dbms_space.create_index_cost('CREATE INDEX IND_TESTE ON TESTE(OBJECT_ID)', :used, :alloc );

PL/SQL procedure successfully completed.

           ALLOC
----------------
     276,824,064


            USED
----------------
      57,682,560

The result is shown in bytes.
To evaluate the impact on storage space for new indexes, this procedure becomes very useful.
It helps to estimate the space needed before issuing the create command so that we don’t have a space problem during creation.

To validate that this is correct, let’s create the actual index.

SQL> CREATE INDEX IND_TESTE ON TESTE(OBJECT_ID);

Index created.

SQL> select segment_name,bytes from dba_segments where segment_name='IND_TESTE';

SEGMENT_NAME              BYTES
-------------------- ----------
IND_TESTE             275103808