Close Menu
Performance

DBMS_SPACE.CREATE_INDEX_COST – Como Estimar o Tamanho de um Índice Antes de Criá-lo

HenriqueBy 7 Mins Read

This post is also available in: English (Inglês)

Antes de criar um índice em uma tabela de produção com milhões de linhas, a pergunta inevitável é: quanto espaço vai consumir?

Criar um índice sem essa estimativa pode causar problemas sérios — tablespace cheio no meio da criação, impacto no storage, ou simplesmente uma surpresa na fatura do cloud. A boa notícia é que o Oracle oferece uma forma nativa de estimar isso sem criar o índice de fato.

Neste artigo, vou mostrar como usar o DBMS_SPACE.CREATE_INDEX_COST para estimar o tamanho de índices antes de criá-los, com exemplos práticos, validação real e limitações que você precisa conhecer.

O Que o CREATE_INDEX_COST Faz

A procedure DBMS_SPACE.CREATE_INDEX_COST recebe um comando CREATE INDEX como parâmetro e retorna duas estimativas:

VariávelO que representa
usedEspaço necessário para os dados do índice (bytes) — o tamanho útil
allocEspaço total que será alocado no tablespace (bytes) — inclui overhead de blocos, extensões e metadados

A diferença entre os dois é normal — o Oracle aloca espaço em extensões (extents), então alloc é sempre maior que used.

Pré-requisitos

Para que a estimativa seja precisa:

  • A tabela onde o índice será criado deve existir
  • As estatísticas da tabela devem estar atualizadas (DBMS_STATS)
  • Quanto mais atualizadas as estatísticas, melhor a estimativa

Exemplo Básico — Índice Simples

Passo 1 — Preparar as variáveis

SET SERVEROUTPUT ON
VARIABLE used_bytes NUMBER
VARIABLE alloc_bytes NUMBER

Passo 2 — Estimar o tamanho

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_orders_date ON orders(order_date)', -
  :used_bytes, -
  :alloc_bytes -
);

Passo 3 — Visualizar o resultado em MB

SELECT
  ROUND(:used_bytes / 1024 / 1024, 2) AS used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS alloc_mb
FROM dual;

Resultado (exemplo):

   USED_MB   ALLOC_MB
---------- ----------
     55.00     264.00

A estimativa indica que o índice vai ocupar ~55 MB de dados úteis e o Oracle vai alocar ~264 MB no tablespace.

Exemplo com Índice Composto (Múltiplas Colunas)

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date)', -
  :used_bytes, -
  :alloc_bytes -
);

SELECT
  ROUND(:used_bytes / 1024 / 1024, 2) AS used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS alloc_mb
FROM dual;

Índices compostos são maiores porque cada entrada no índice armazena os valores de todas as colunas da chave.

Exemplo com Tablespace Específico

Se você quer estimar considerando um tablespace com configuração de extensão diferente:

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_orders_date ON orders(order_date) TABLESPACE idx_ts', -
  :used_bytes, -
  :alloc_bytes -
);

O Oracle considera o tamanho de extensão do tablespace idx_ts para calcular o alloc.

Exemplo Completo — Criação, Estimativa e Validação

Vamos montar um cenário do zero para demonstrar a precisão da estimativa.

Criar tabela de teste

CREATE TABLE teste AS SELECT * FROM all_objects;

-- Inserir mais dados para simular volume
INSERT INTO teste (SELECT * FROM teste);
-- Repetir até ter volume significativo (ex: ~5 milhões de linhas)

COMMIT;

SELECT COUNT(*) FROM teste;

Coletar estatísticas

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'TESTE',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE
  );
END;
/

Estimar o tamanho do índice

VARIABLE used_bytes NUMBER
VARIABLE alloc_bytes NUMBER

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_teste_objid ON teste(object_id)', -
  :used_bytes, -
  :alloc_bytes -
);

SELECT
  ROUND(:used_bytes / 1024 / 1024, 2) AS estimated_used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS estimated_alloc_mb
FROM dual;

Criar o índice de fato e comparar

CREATE INDEX idx_teste_objid ON teste(object_id);

SELECT
  segment_name,
  ROUND(bytes / 1024 / 1024, 2) AS actual_mb
FROM dba_segments
WHERE segment_name = 'IDX_TESTE_OBJID';

Comparar estimativa vs real

Estimativa alloc:  264.00 MB
Tamanho real:      262.44 MB
Diferença:         ~0.6%

A estimativa costuma ser muito próxima do valor real quando as estatísticas estão atualizadas.

Estimando Tamanho de Tabelas com CREATE_TABLE_COST

O mesmo pacote DBMS_SPACE oferece o CREATE_TABLE_COST para estimar o tamanho de tabelas. Útil para planejamento de capacidade:

VARIABLE used_bytes NUMBER
VARIABLE alloc_bytes NUMBER

EXEC DBMS_SPACE.CREATE_TABLE_COST( -
  'USERS',          -- tablespace_name
  8000,             -- avg_row_size (bytes)
  1000000,          -- row_count
  0,                -- pct_free
  :used_bytes,      --
  :alloc_bytes      --
);

SELECT
  ROUND(:used_bytes / 1024 / 1024, 2) AS used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS alloc_mb
FROM dual;

Limitações e Cuidados

Antes de confiar cegamente na estimativa, conheça as limitações:

LimitaçãoImpacto
Estatísticas desatualizadasSe as estatísticas da tabela estão velhas ou inexistentes, a estimativa será imprecisa
Índices BitmapO CREATE_INDEX_COST não funciona para estimar índices bitmap
Índices Domain (Oracle Text, Spatial)Não suportado
Function-based indexesFunciona, mas precisa que as estatísticas da expressão estejam coletadas
Tabelas particionadasA estimativa é para a tabela inteira, não por partição individual
Compressão de índiceSe você vai usar COMPRESS, o tamanho real será menor que a estimativa
Colunas LOBNão se aplica — LOBs não podem ser indexados diretamente

Regra de ouro

Sempre colete estatísticas atualizadas antes de rodar o CREATE_INDEX_COST. Estatísticas velhas = estimativa errada.

-- Coletar antes de estimar
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'NOME_DA_TABELA');

Script Reutilizável

Salve este script para usar sempre que precisar estimar índices:

-- estimate_index.sql
-- Uso: @estimate_index.sql
-- Altere o CREATE INDEX abaixo para o seu cenário

SET SERVEROUTPUT ON
VARIABLE used_bytes NUMBER
VARIABLE alloc_bytes NUMBER

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_nome ON schema.tabela(coluna1, coluna2)', -
  :used_bytes, -
  :alloc_bytes -
);

SELECT
  'Estimativa' AS tipo,
  ROUND(:used_bytes / 1024 / 1024, 2) AS used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS alloc_mb,
  ROUND(:alloc_bytes / 1024 / 1024 / 1024, 2) AS alloc_gb
FROM dual;

Conclusão

O DBMS_SPACE.CREATE_INDEX_COST é uma ferramenta essencial para qualquer DBA que precisa planejar antes de agir. Em ambientes de produção com tabelas de centenas de milhões de linhas, criar um índice sem estimar o espaço é um risco desnecessário.

O fluxo recomendado antes de criar qualquer índice em produção:

  1. Coletar estatísticas atualizadas da tabela
  2. Estimar o tamanho com CREATE_INDEX_COST
  3. Verificar se o tablespace tem espaço suficiente
  4. Criar o índice com confiança

Simples, rápido e evita surpresas.

Referências:

Share.
Add A Comment
Leave A Reply