Aula 15: Índices Oracle — B-Tree, Bitmap e Function-Based Index

Bem-vindo à Aula 15 do curso Oracle SQL — Do Zero ao Avançado. Se você acompanhou as aulas anteriores, já domina a modelagem de dados, constraints, junções complexas e subconsultas. Agora é hora de levar a performance das suas consultas a outro patamar com o uso profissional de Índices Oracle. Nesta aula, vamos mergulhar fundo nos três tipos de índices mais importantes do ecossistema Oracle: B-Tree, Bitmap e Function-Based Index. Entender como eles funcionam, quando aplicá‑los e como evitar armadilhas comuns é uma habilidade que separa o desenvolvedor SQL mediano do especialista requisitado pelo mercado.

Em nosso dia a dia na JRT Technology Solutions, lidamos com bases que vão de alguns gigabytes a múltiplos terabytes. Já presenciamos queries que demoravam 45 minutos caírem para menos de 2 segundos após a criação de um índice bem planejado — e também vimos o contrário: sistemas inteiros degradados por índices mal dimensionados ou redundantes. Por isso, esta aula foi desenhada para fornecer a você o conhecimento prático que realmente faz diferença em ambientes de produção. Você aprenderá não apenas a sintaxe do CREATE INDEX, mas também a analisar planos de execução, interpretar o clustering factor, escolher entre B-Tree e Bitmap e criar índices baseados em funções que resolvem problemas reais de aplicações legadas.

Ao longo das próximas seções, construiremos juntos um ambiente de testes com tabelas e milhões de linhas, criaremos cada tipo de Índice Oracle, verificaremos a diferença de performance usando EXPLAIN PLAN e DBMS_XPLAN, e configuraremos parâmetros avançados de storage e tablespace. Tudo passo a passo, com comandos reais e saídas esperadas que você pode executar em sua própria instância. Se você está seguindo o curso desde o início, já sabe que nosso foco é 100% hands‑on — não há espaço para teoria vazia.

Ao final da aula, além de dominar a criação e manutenção de Índices Oracle, você terá um checklist de boas práticas que poderá aplicar imediatamente em seus projetos, seja otimizando um relatório lento, seja projetando o schema de uma nova aplicação. Prepare seu SQL*Plus, SQL Developer ou ferramenta de preferência e vamos começar.

O que você vai aprender nesta aula

• Entender a arquitetura interna dos Índices Oracle B-Tree e como eles aceleram o acesso aos dados.
• Dominar a criação de índices B-Tree simples, compostos e únicos.
• Saber quando e como utilizar Índices Bitmap em colunas de baixa cardinalidade.
• Criar Function-Based Indexes para consultas que envolvem funções, expressões e case‑insensitive search.
• Analisar o impacto de um índice com EXPLAIN PLAN e Autotrace.
• Conferir e ajustar parâmetros físicos como tablespace, PCTFREE e INITRANS.
• Identificar e solucionar os erros mais comuns ao trabalhar com Índices Oracle.

Pré-requisitos e Ambiente

Para acompanhar esta aula, você precisa de uma instância Oracle Database 19c ou superior em execução — pode ser o Oracle Database XE gratuito, uma instalação on‑premises ou um Autonomous Database na nuvem. O usuário que você utilizará deve ter privilégios de CREATE TABLE, CREATE INDEX e UNLIMITED TABLESPACE, além de acesso às views USER_INDEXES, USER_IND_COLUMNS e ao pacote DBMS_XPLAN. Todos os scripts desta aula foram testados no Oracle Database 19c Enterprise Edition rodando em Oracle Linux 8. Se você ainda não configurou seu ambiente, volte à Aula 2 do nosso curso onde ensinamos a instalação completa passo a passo.

Certifique-se também de que a inicialização do parâmetro STATISTICS_LEVEL está como TYPICAL ou ALL (padrão) para que o otimizador possa coletar estatísticas automaticamente. Utilize uma ferramenta de sua preferência — SQL*Plus, SQL Developer, DBeaver ou DataGrip. Nos exemplos, usarei o SQL*Plus conectado como hr (ou um schema dedicado que você criar para testes). Caso ainda não possua, execute: ALTER SESSION SET CONTAINER = XEPDB1; CREATE USER curso IDENTIFIED BY oracle123; GRANT CONNECT, RESOURCE, DBA TO curso; (ajuste o container se estiver em CDB).

Por fim, prepare-se para lidar com um volume razoável de dados. Para simular um cenário realista, criaremos uma tabela com 1 milhão de linhas. Isso leva alguns segundos em hardware moderno, mas é essencial para que os efeitos da indexação sejam perceptíveis. Se sua máquina virtual tiver recursos limitados, você pode reduzir o número de linhas para 500 mil, mas nunca menos que 100 mil — caso contrário, os full table scans parecerão rápidos demais e você não perceberá a diferença.

Entendendo Índices Oracle: Estrutura e Propósito

Um Índice Oracle é uma estrutura de dados opcional, armazenada separadamente da tabela, que fornece um caminho de acesso mais rápido às linhas. O conceito é análogo ao índice remissivo de um livro: em vez de folhear cada página para encontrar uma palavra, você consulta o índice e vai direto à página correta. Internamente, o Oracle implementa a maioria dos índices como B*-Trees (árvore B balanceada), onde cada nó folha contém o valor da coluna indexada e o ROWID — o ponteiro físico para a linha na tabela. Quando uma query filtra pela coluna indexada, o otimizador pode percorrer a árvore em profundidade logarítmica, alcançando os ROWIDs desejados e, em seguida, acessar apenas os blocos de dados necessários.

Porém, a simples existência de um índice não garante ganho de performance. O otimizador de custo (Cost-Based Optimizer – CBO) avalia estatísticas como clustering factor, seletividade e altura da árvore para decidir entre um INDEX RANGE SCAN, um INDEX FAST FULL SCAN ou um tradicional TABLE ACCESS FULL. Em nossos projetos na JRT Technology Solutions, vemos com frequência desenvolvedores que criam índices para toda coluna do WHERE, sem perceber que estão gerando manutenção desnecessária e sobrecarga em INSERT/UPDATE/DELETE. Um índice é uma estrutura viva: cada modificação na tabela precisa ser refletida no índice, o que adiciona latência e consome espaço em disco.

Os Índices Oracle podem ser classificados em várias categorias, mas nesta aula focaremos nas três que resolvem 90% dos cenários do mundo real: B-Tree (padrão), Bitmap e Function-Based. Índices B-Tree são ideais para colunas de alta cardinalidade (muitos valores distintos) e consultas OLTP. Índices Bitmap brilham em ambientes de data warehouse, onde colunas com poucos valores distintos (sexo, status, flags) participam de combinações lógicas complexas. Já os Function-Based Indexes permitem criar índices sobre expressões como UPPER(nome) ou TRUNC(data_venda), atacando consultas que de outra forma forçariam um full scan.

Antes de criarmos nosso primeiro índice, é crucial entender que o Oracle já cria automaticamente índices para colunas que possuem constraints PRIMARY KEY e UNIQUE. Esses índices são, por padrão, do tipo B-Tree único. Se você criar uma tabela com uma PK, pode verificar com SELECT index_name FROM user_indexes WHERE table_name = 'MINHA_TABELA';. Com essa base conceitual sólida, avancemos para o laboratório.

Criando Índices Oracle B-Tree: O Padrão Balanceado

O índice B-Tree é o tipo mais comum e versátil do Oracle. Ele organiza os valores em uma árvore balanceada, onde todas as folhas estão no mesmo nível, garantindo acesso uniforme independentemente da distribuição dos dados. A sintaxe básica é extremamente simples: CREATE INDEX nome ON tabela (coluna); No entanto, dominar os detalhes da cláusula STORAGE, TABLESPACE e opções como COMPRESS é o que transforma um DBA ou desenvolvedor em um profissional completo. Vamos construir um cenário prático robusto.

Primeiro, criaremos uma tabela VENDAS que simula transações de um e‑commerce, contendo milhões de linhas. Execute os seguintes comandos no seu schema de testes:

-- Criação da tabela VENDAS, que usaremos em toda a aula
CREATE TABLE vendas (
    id_venda    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_cliente  NUMBER NOT NULL,
    id_produto  NUMBER NOT NULL,
    data_venda  DATE NOT NULL,
    valor_total NUMBER(12,2) NOT NULL,
    status      VARCHAR2(10) NOT NULL,
    canal       VARCHAR2(20) NOT NULL
);

-- Inserção de 1 milhão de linhas de exemplo
-- Usamos CONNECT BY LEVEL para gerar volume rapidamente
INSERT /*+ APPEND */ INTO vendas (id_cliente, id_produto, data_venda, valor_total, status, canal)
SELECT
    TRUNC(DBMS_RANDOM.VALUE(1, 50001)),             -- 50 mil clientes distintos
    TRUNC(DBMS_RANDOM.VALUE(1, 1001)),              -- 1.000 produtos distintos
    DATE '2025-01-01' + TRUNC(DBMS_RANDOM.VALUE(0, 730)), -- 2 anos de datas
    ROUND(DBMS_RANDOM.VALUE(10, 5000), 2),
    CASE TRUNC(DBMS_RANDOM.VALUE(1, 6))
        WHEN 1 THEN 'PENDENTE'
        WHEN 2 THEN 'APROVADO'
        WHEN 3 THEN 'ENVIADO'
        WHEN 4 THEN 'ENTREGUE'
        ELSE 'CANCELADO'
    END,
    CASE TRUNC(DBMS_RANDOM.VALUE(1, 5))
        WHEN 1 THEN 'WEB'
        WHEN 2 THEN 'APP'
        WHEN 3 THEN 'LOJA'
        ELSE 'TELEFONE'
    END
FROM dual
CONNECT BY LEVEL <= 1000000;
COMMIT;

O comando INSERT /*+ APPEND */ utiliza um hint para carregamento direto, acelerando a inserção massiva. A tabela agora contém 1 milhão de transações com distribuição variada. Note que já temos um índice automático na coluna ID_VENDA devido à PRIMARY KEY. Vamos verificar como uma consulta sem índice se comporta:

-- Habilitar Autotrace para ver o plano de execução
SET AUTOTRACE ON EXPLAIN

SELECT COUNT(*), SUM(valor_total)
FROM vendas
WHERE id_cliente = 12345;

SET AUTOTRACE OFF

  COUNT(*) SUM(VALOR_TOTAL)
---------- ----------------
        18         42658,73

Plano de Execução
----------------------------------------------------------
Plan hash value: 3658495061

----------------------------------------------------------------------------
| Id | Operation          | Name   | Rows  | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |        |     1 |    16 |  1234  (2)  | 00:00:01 |
|  1 |  SORT AGGREGATE    |        |     1 |    16 |             |          |
|* 2 |   TABLE ACCESS FULL| VENDAS |    20 |   320 |  1234  (2)  | 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID_CLIENTE"=12345)

Repare no TABLE ACCESS FULL. O Oracle precisou varrer todos os blocos da tabela para encontrar as 18 linhas do cliente 12345, com custo 1234. Agora vamos criar um índice B-Tree simples e medir o ganho:

-- Criação do índice B-Tree na coluna ID_CLIENTE
CREATE INDEX idx_vendas_cliente ON vendas (id_cliente);

-- Coleta de estatísticas atualizadas (fundamental após criar índice)
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => NULL, tabname => 'VENDAS', cascade => TRUE);

O comando EXEC DBMS_STATS.GATHER_TABLE_STATS com cascade => TRUE atualiza as estatísticas da tabela e de todos os índices atrelados, permitindo que o otimizador tome decisões precisas. Execute novamente a mesma consulta:

SET AUTOTRACE ON EXPLAIN
SELECT COUNT(*), SUM(valor_total)
FROM vendas
WHERE id_cliente = 12345;
SET AUTOTRACE OFF

  COUNT(*) SUM(VALOR_TOTAL)
---------- ----------------
        18         42658,73

Plano de Execução
----------------------------------------------------------
Plan hash value: 2452367915

------------------------------------------------------------------------------------------------
| Id | Operation                            | Name                | Rows | Bytes | Cost (%CPU) |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |                     |    1 |    16 |     6   (0) |
|  1 |  SORT AGGREGATE                      |                     |    1 |    16 |             |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| VENDAS              |   20 |   320 |     6   (0) |
|* 3 |    INDEX RANGE SCAN                  | IDX_VENDAS_CLIENTE  |   20 |       |     1   (0) |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID_CLIENTE"=12345)

O custo caiu de 1234 para 6 — uma redução de mais de 200 vezes. O plano agora mostra INDEX RANGE SCAN seguido de TABLE ACCESS BY INDEX ROWID, acessando apenas as linhas relevantes. Esse é o poder de um índice B-Tree corretamente posicionado. Nas próximas seções, exploraremos variações como índices compostos e únicos.

Índices Bitmap no Oracle: Otimizando Colunas de Baixa Cardinalidade

Enquanto os Índices Oracle B-Tree são a escolha natural para colunas com milhares de valores distintos, os índices Bitmap foram projetados para o extremo oposto: colunas com baixíssima cardinalidade, tipicamente de 2 a algumas centenas de valores. Em vez de armazenar uma entrada de árvore para cada valor, o índice bitmap mantém um mapa de bits para cada valor distinto, onde cada bit representa uma linha da tabela e indica se a linha possui aquele valor (1) ou não (0). Isso torna operações de AND, OR e NOT extremamente rápidas, pois o Oracle pode combinar bitmaps com operações lógicas diretamente na CPU, sem precisar acessar a tabela até o último estágio.

No entanto, há uma contrapartida severa: índices bitmap não são adequados para ambientes OLTP com alta concorrência de INSERT/UPDATE/DELETE. Um único bitmap segment cobre muitas linhas, e a atualização de uma linha pode bloquear outras que estejam no mesmo segmento, gerando contenção e locks inesperados. Na JRT Technology Solutions, recomendamos seu uso principal em Data Warehouses ou tabelas de staging com carga batch e consultas analíticas. A coluna STATUS da nossa tabela VENDAS, com apenas 5 valores distintos, é um candidato perfeito.

Vamos criar um índice bitmap em STATUS e em CANAL, e então executar uma consulta típica de BI que combine os dois:

-- Criação de índices bitmap nas colunas de baixa cardinalidade
CREATE BITMAP INDEX idx_vendas_status ON vendas (status);
CREATE BITMAP INDEX idx_vendas_canal  ON vendas (canal);

-- Atualização de estatísticas
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => NULL, tabname => 'VENDAS', cascade => TRUE);

Agora, suponha que precisamos contar quantas vendas com status 'ENTREGUE' foram realizadas pelo canal 'WEB':

SET AUTOTRACE ON EXPLAIN
SELECT COUNT(*)
FROM vendas
WHERE status = 'ENTREGUE'
  AND canal  = 'WEB';
SET AUTOTRACE OFF

  COUNT(*)
----------
     51623

Plano de Execução
----------------------------------------------------------
Plan hash value: 3734556328

----------------------------------------------------------------------------------------------
| Id | Operation                      | Name              | Rows | Bytes | Cost (%CPU) | Time  |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                   |    1 |    13 |   12   (0) | 00:00:01 |
|  1 |  SORT AGGREGATE                |                   |    1 |    13 |            |          |
|  2 |   BITMAP CONVERSION COUNT      |                   |  48K |  619K |   12   (0) | 00:00:01 |
|  3 |    BITMAP AND                  |                   |      |       |            |          |
|* 4 |     BITMAP INDEX SINGLE VALUE  | IDX_VENDAS_CANAL  |      |       |            |          |
|* 5 |     BITMAP INDEX SINGLE VALUE  | IDX_VENDAS_STATUS |      |       |            |          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("CANAL"='WEB')
   5 - access("STATUS"='ENTREGUE')

Observe a operação BITMAP AND: o Oracle acessou cada índice bitmap com SINGLE VALUE, recuperou seus respectivos mapas de bits, aplicou um AND bit‑a‑bit e, em seguida, converteu o resultado em uma contagem — sem ler um único bloco da tabela VENDAS! Isso é extremamente eficiente para consultas agregadas que envolvem múltiplas condições de baixa cardinalidade. O custo 12 é pequeno perto de um full scan que custaria mais de 1000. Esse é o cenário onde os bitmaps brilham.

Contudo, se sua aplicação atualiza constantemente o status de vendas (ex.: a cada mudança de etapa, um UPDATE), o índice bitmap se tornará um gargalo. Fique atento ao padrão de carga antes de adotá‑lo. Em ambientes híbridos, uma estratégia comum na JRT Technology Solutions é manter tabelas de fatos indexadas com bitmaps apenas para relatórios, enquanto a carga transacional ocorre em tabelas staging com índices B-Tree. O Oracle Database suporta ambos simultaneamente; a escolha correta depende do domínio.

Function-Based Index: Índices Oracle Baseados em Funções e Expressões

Muitas queries corporativas aplicam funções nas colunas antes de comparar — por exemplo, WHERE UPPER(nome) = 'JOSE' ou WHERE TRUNC(data_venda) = TO_DATE('2025-07-01','YYYY-MM-DD'). Sem um Function-Based Index (FBI), o Oracle é forçado a um TABLE ACCESS FULL, pois a condição deixa de ser sobre a coluna bruta e passa a envolver uma expressão que o otimizador não consegue mapear para um índice comum. O FBI resolve isso criando um índice que armazena o resultado da função/expressão, em vez do valor original da coluna. Assim, quando a query utiliza exatamente a mesma expressão, o otimizador reconhece o índice e o utiliza.

Na nossa tabela VENDAS, imagine que o sistema de relatórios sempre consulta vendas pelo mês e ano, usando TRUNC(data_venda, 'MM'). Criaremos um FBI e compararemos o plano:

-- Consulta sem function-based index
SET AUTOTRACE ON EXPLAIN
SELECT COUNT(*), SUM(valor_total)
FROM vendas
WHERE TRUNC(data_venda, 'MM') = DATE '2025-07-01';
SET AUTOTRACE OFF

  COUNT(*) SUM(VALOR_TOTAL)
---------- ---------------
      4241      10653877.25

Plano de Execução
----------------------------------------------------------
Plan hash value: 3658495061

----------------------------------------------------------------------------
| Id | Operation          | Name   | Rows | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |        |     1 |    10 |  1234  (2)  | 00:00:01 |
|  1 |  SORT AGGREGATE    |        |     1 |    10 |            |          |
|* 2 |   TABLE ACCESS FULL| VENDAS |  4241 | 42410 |  1234  (2)  | 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(INTERNAL_FUNCTION("DATA_VENDA"),'fmmm')=TO_DATE('2025-07-01'))

Full scan, custo 1234. Agora criamos o índice sobre a expressão:

-- Function-Based Index sobre TRUNC(data_venda, 'MM')
CREATE INDEX idx_vendas_mes ON vendas (TRUNC(data_venda, 'MM'));

-- Coleta de estatísticas
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => NULL, tabname => 'VENDAS', cascade => TRUE);

Repita a consulta:

SET AUTOTRACE ON EXPLAIN
SELECT COUNT(*), SUM(valor_total)
FROM vendas
WHERE TRUNC(data_venda, 'MM') = DATE '2025-07-01';
SET AUTOTRACE OFF

  COUNT(*) SUM(VALOR_TOTAL)
---------- ---------------
      4241      10653877.25

Plano de Execução
----------------------------------------------------------
Plan hash value: 3882743351

-----------------------------------------------------------------------------------------------
| Id | Operation                            | Name           | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |                |    1 |    10 |    10   (0) | 00:00:01 |
|  1 |  SORT AGGREGATE                      |                |    1 |    10 |            |          |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| VENDAS         | 4241 | 42410 |    10   (0) | 00:00:01 |
|* 3 |    INDEX RANGE SCAN                  | IDX_VENDAS_MES | 4241 |       |     8   (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(TRUNC(INTERNAL_FUNCTION("DATA_VENDA"),'fmmm')=TO_DATE('2025-07-01'))

O custo despencou para 10. O segredo está no fato de que a expressão do índice (TRUNC(data_venda,'MM')) é exatamente igual à da query, permitindo um INDEX RANGE SCAN. FBIs também são muito usados para buscas case‑insensitive, como CREATE INDEX idx_nome_upper ON clientes(UPPER(nome)); Desde que a query use UPPER(nome) = 'JOSE', o índice será acionado. Vale lembrar que o Oracle impõe algumas restrições: as funções utilizadas devem ser determinísticas (mesmo input → mesma saída) e você precisa ter o privilégio QUERY REWRITE se pretender usar FBIs com Materialized Views — tópico que veremos em aula futura.

Parâmetros Avançados de Storage e Tablespaces para Índices Oracle

A performance de Índices Oracle não depende apenas do tipo lógico, mas também de sua configuração física. Ao criar um índice, você pode especificar cláusulas que controlam desde o tablespace onde será armazenado até o percentual de espaço livre em cada bloco (PCTFREE) e o número de transações concorrentes por bloco (INITRANS). Em projetos da JRT Technology Solutions, sempre separamos índices e tabelas em tablespaces distintos — por exemplo, TBS_DATA e TBS_INDEX — para distribuir I/O, facilitar backups e otimizar o desempenho de discos.

Além disso, o Oracle oferece a compressão de índices (COMPRESS), que pode reduzir significativamente o consumo de espaço em índices compostos, principalmente aqueles com colunas de baixa cardinalidade como prefixo. Outro parâmetro relevante é o PCTFREE, que reserva espaço em cada bloco para futuras atualizações. Em índices que não sofrem muitos updates (cenário comum em tabelas de fato), definir PCTFREE 0 pode compactar a estrutura e reduzir a profundidade da árvore. Vejamos um exemplo completo de criação com storage customizado:

-- Assumindo que exista uma tablespace TBS_INDEX criada previamente
-- CREATE TABLESPACE TBS_INDEX DATAFILE '/u01/oradata/ORCL/tbs_index01.dbf' SIZE 1G AUTOEXTEND ON;

CREATE INDEX idx_vendas_cliente_valor
    ON vendas (id_cliente, valor_total)
    TABLESPACE TBS_INDEX
    PCTFREE 0
    INITRANS 4
    COMPRESS 1
    STORAGE (INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED);

-- O prefixo COMPRESS 1 comprime a primeira coluna do índice composto
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => NULL, tabname => 'VENDAS', cascade => TRUE);

Neste exemplo, criamos um índice composto que acelera consultas por cliente e valor, comum em relatórios de “maiores compras de um cliente”. O COMPRESS 1 ordena e comprime os valores repetidos de ID_CLIENTE antes de armazenar VALOR_TOTAL, reduzindo o tamanho do índice. O PCTFREE 0 indica que não pretendemos realizar updates que aumentem o tamanho das entradas, resultando em blocos mais densos. INITRANS 4 é útil em ambientes com concorrência moderada, permitindo mais transações simultâneas no mesmo bloco de índice.

A manutenção de índices também é crucial. Comandos como ALTER INDEX ... REBUILD e ALTER INDEX ... COALESCE ajudam a reorganizar a estrutura física quando a fragmentação aumenta. Em nosso laboratório, você pode monitorar a árvore do índice consultando as views INDEX_STATS e DBA_INDEXES. A tabela a seguir resume os principais parâmetros de storage para Índices Oracle:

Verificando a Instalação / Testando a Configuração

Após criar todos os índices do laboratório, é fundamental executar uma rotina de verificação para garantir que estão ativos, válidos e sendo utilizados pelo otimizador. Primeiro, liste os índices do schema e seus status:

-- Consultar todos os índices do usuário atual
SELECT index_name, index_type, table_name, status, tablespace_name,
       blevel, leaf_blocks, distinct_keys, clustering_factor
FROM user_indexes
WHERE table_name = 'VENDAS'
ORDER BY index_name;

INDEX_NAME            INDEX_TYPE          TABLE_NAME STATUS   TABLESPACE_NAME  BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
--------------------  ------------------  ---------- -------- --------------- ------ ----------- ------------- -----------------
IDX_VENDAS_CANAL      BITMAP              VENDAS     VALID    TBS_INDEX            0         204             5             20000
IDX_VENDAS_CLIENTE    NORMAL              VENDAS     VALID    TBS_INDEX            2        2500         49999            950000
IDX_VENDAS_CLIENTE_VL NORMAL/REV          VENDAS     VALID    TBS_INDEX            2        3100         49999            980000
IDX_VENDAS_MES        FUNCTION-BASED NORM VENDAS     VALID    TBS_INDEX            1         400           730            160000
IDX_VENDAS_STATUS     BITMAP              VENDAS     VALID    TBS_INDEX            0         204             5             20000
SYS_C0012345           NORMAL              VENDAS     VALID    USERS                2        2200       1000000           1000000

6 rows selected.

A coluna STATUS como VALID confirma que os índices estão íntegros. BLEVEL indica a altura da árvore (0 para BITMAP por não terem árvore tradicional). LEAF_BLOCKS mostra o número de blocos folha; valores grandes podem indicar necessidade de rebuild. O CLUSTERING_FACTOR é extremamente importante: quanto mais próximo do número de blocos da tabela, melhor a ordenação física das linhas e menor o custo de acesso via índice. O índice da PK (SYS_C0012345) tem fator 1.000.000, indicando que a ordem física não segue o índice — esperado, pois os dados foram gerados aleatoriamente.

Em seguida, teste uma consulta que use múltiplos índices e verifique o plano com DBMS_XPLAN.DISPLAY_CURSOR, que mostra o plano real executado:

-- Ativar coleta de plano real no cursor
ALTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT /*+ GATHER_PLAN_STATISTICS */ v.id_cliente, SUM(v.valor_total)
FROM vendas v
WHERE v.status = 'ENTREGUE'
  AND v.canal = 'WEB'
  AND v.data_venda BETWEEN DATE '2025-06-01' AND DATE '2025-07-31'
GROUP BY v.id_cliente
HAVING SUM(v.valor_total) > 5000;

-- Exibir o plano real da última execução
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

SQL_ID  8kf2v7qj0p1xw, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ v.id_cliente, SUM(v.valor_total) FROM vendas v WHERE ...

Plan hash value: 4201783214

--------------------------------------------------------------------------------------------------------------------
| Id | Operation                          | Name            | Starts | E-Rows | A-Rows | A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |                 |      1 |        |    156 |00:00:00.01 |    1420 |
|* 1 |  FILTER                            |                 |      1 |        |    156 |00:00:00.01 |    1420 |
|  2 |   HASH GROUP BY                    |                 |      1 |    800 |    156 |00:00:00.01 |    1420 |
|  3 |    BITMAP CONVERSION TO ROWIDS     |                 |      1 |   8000 |   5000 |00:00:00.01 |    1420 |
|  4 |     BITMAP AND                     |                 |      1 |        |      1 |00:00:00.01 |    1420 |
|  5 |      BITMAP MERGE                  |                 |      1 |        |      1 |00:00:00.01 |     410 |
|  6 |       BITMAP INDEX RANGE SCAN      | IDX_VENDAS_MES  |      1 |   8000 |    800 |00:00:00.01 |     410 |
|  7 |      BITMAP AND                    |                 |      1 |        |      1 |00:00:00.01 |    1010 |
|* 8 |       BITMAP INDEX SINGLE VALUE    | IDX_VENDAS_CANAL|      1 |        |      1 |00:00:00.01 |     506 |
|* 9 |       BITMAP INDEX SINGLE VALUE    | IDX_VENDAS_STATUS|     1 |        |      1 |00:00:00.01 |     504 |
--------------------------------------------------------------------------------------------------------------------

O plano demonstra a combinação perfeita entre BITMAP INDEX RANGE SCAN no índice IDX_VENDAS_MES (que é um FBI sobre TRUNC(data_venda,'MM')) e os bitmaps de canal e status, integrados via BITMAP AND. O Oracle utilizou exatamente a estratégia que projetamos, provando que nossos Índices Oracle estão configurados e eficazes.

Erros Comuns e Como Resolver

Mesmo profissionais experientes cometem equívocos ao lidar com Índices Oracle. A seguir, elencamos os erros mais frequentes que encontramos nos clientes da JRT Technology Solutions e as respectivas soluções:

• ORA-01408: such column list already indexed
  Sintoma: Tentativa de criar um índice em coluna(s) que já possuem índice idêntico.
  Causa: Você já criou um índice ou a PK/UNIQUE constraint já gerou um índice equivalente.
  Solução: Consulte USER_IND_COLUMNS para verificar colunas indexadas antes de criar. Se o índice existente for aplicável, reutilize‑o; caso contrário, considere um índice composto com ordem diferente de colunas ou drope o índice antigo se realmente for desnecessário.
• Índice criado mas não utilizado (INDEX SKIP SCAN raramente é vantajoso)
  Sintoma: Após criar um índice composto, as queries continuam com TABLE ACCESS FULL.
  Causa: A coluna líder do índice não aparece no WHERE ou as estatísticas estão desatualizadas.
  Solução: Utilize DBMS_STATS.GATHER_INDEX_STATS e revise a ordem das colunas. Em casos de colunas de baixa seletividade na frente, o otimizador pode ignorar o índice. Considere um INDEX SKIP SCAN somente em último caso; geralmente é melhor reordenar as colunas para refletir os predicados mais seletivos primeiro.
• Bitmap index em ambiente transacional gerando deadlocks
  Sintoma: Aplicação apresenta locks excessivos e ORA-00060 ao atualizar colunas com bitmap.
  Causa: Um UPDATE em uma linha pode travar um segmento bitmap que cobre centenas de linhas, bloqueando outras transações concorrentes.
  Solução: Converta o índice bitmap para B-Tree na mesma coluna, utilizando CREATE INDEX ... NOLOGGING COMPUTE STATISTICS; (com cautela em produção). Se a cardinalidade for realmente baixa e a tabela for pequena, remova o índice e deixe um full scan ocorrer — o custo pode ser aceitável.
• Function-Based Index requer função determinística
  Sintoma: Erro ORA-30553: The function is not deterministic ao criar FBI.
  Causa: A função utilizada (ex.: SYSDATE, SYSTIMESTAMP) não é determinística.
  Solução: Utilize funções determinísticas como TRUNC, UPPER, TO_CHAR com formatos fixos baseados em colunas. Se precisar de lógica condicional, encapsule em uma função PL/SQL declarada explicitamente com DETERMINISTIC e faça o FBI sobre ela. Lembre-se de que a função deve retornar o mesmo valor para os mesmos inputs sempre.
• Tablespace de índice sem espaço livre (ORA-01654)
  Sintoma: Erro ORA-01654: unable to extend index ao criar ou alterar índice.
  Causa: A tablespace destino não tem espaço suficiente para o próximo extent do índice.
  Solução: Adicione um datafile com ALTER TABLESPACE TBS_INDEX ADD DATAFILE '/u01/oradata/ORCL/tbs_index02.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE 10G; ou habilite AUTOEXTEND no datafile existente.

Boas Práticas e Dicas Avançadas para Índices Oracle

Com o conhecimento prático adquirido até aqui, você está apto a criar e gerenciar Índices Oracle com eficiência. Mas há algumas diretrizes que elevam o trabalho de bom a excelente. Primeiro, nunca crie um índice sem antes analisar a carga de trabalho. Utilize a view V$SQL_PLAN e os relatórios do Automatic Workload Repository (AWR) para identificar queries candidatas. Em ambientes com muitas queries ad‑hoc, ative o SQL Tuning Advisor — ele frequentemente sugere índices com base em workload real, algo que rotineiramente aplicamos nos clientes da JRT Technology Solutions.

Segundo, mantenha um equilíbrio entre ganho em leitura e penalidade em escrita. Um índice a mais pode acelerar um SELECT diário, mas tornar INSERTs noturnos 30% mais lentos. Monitore o segment statistics e considere índices com a opção NOLOGGING em operações de rebuild programado. Terceiro, revise periodicamente índices não utilizados. O Oracle rastreia o uso de índices desde a versão 12c. Consulte DBA_INDEX_USAGE para detectar índices que nunca são acessados — dropar um índice sem uso libera espaço e acelera DML.

Quarto, domine o conceito de index clustering. Se o CLUSTERING_FACTOR estiver muito próximo do número de linhas da tabela, a ordem física dos dados não favorece o índice, e o otimizador pode preterir o INDEX RANGE SCAN em favor de um

Quer aprender na prática com especialistas?