Como estimar tamanho do índice

Este post também está disponível em: English

Para estimar o tamanho de um índice antes de criá-lo, podemos usar a package DBMS_SPACE. Isso é útil para avaliar o impacto de criar um novo índice ou alterar os parâmetros de um índice existente.

DBMS_SPACE.CREATE_INDEX_COST

A sintaxe de uma procedure é apresentada da seguinte forma:

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 );

Substitua pelo comando para criar o índice. A variável used estima o espaço necessário para os dados do índice. Já a variável alloc estima o espaço que será alocado no tablespace.

Pontos a considerar antes de executar o comando:

• A tabela onde o índice será criado já deve existir.
• As estatísticas dessa tabela precisam estar atualizadas.

Ambiente de teste

Com a breve introdução acima, vamos preparar um ambiente para realizarmos o teste.

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.

Criado a tabela, vamos coletar estatísticas dela.

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.

Agora vamos ver qual o tamanho do índice para a coluna OBJECT_ID.

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

O resultado é mostrado em bytes.
Para avaliar o impacto no espaço de armazenamento para novos índices, esse procedimento se torna muito útil.
Ele ajuda a estimar o espaço necessário antes de emitir o comando create para que não tenhamos problema de espaço durante a criação.

Para validar se está correto, vamos criar o índice de fato.

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