TUNING

Roberto Sobrinho

09/09/2024

Índices Virtuais no Oracle: Como Testar o Desempenho Sem Criar o Índice Fisicamente

Sabe quando você está tentando otimizar a performance das consultas no banco de dados, mas tem aquele receio de sair criando índices sem saber se realmente vai melhorar? Pois é, nesse momento os índices virtuais entram em cena. Eles são como um “test drive” de índices, que te permitem simular a criação de um índice para ver se ele vai ajudar ou não, sem precisar criá-lo de verdade.

Neste post, vou te mostrar como funciona esse truque, passo a passo, com exemplos práticos. Assim, você pode testar o impacto de um índice antes de mergulhar de cabeça e decidir se vale a pena criá-lo de fato no banco de dados. Vamos nessa?

O Que é um Índice Virtual?

Em poucas palavras, um índice virtual é um índice “fake”. Ele é registrado no banco de dados, mas não existe fisicamente. Ou seja, ele não ocupa espaço em disco e nem afeta diretamente as consultas. O que ele faz é permitir que você simule o comportamento de um índice nas suas consultas, antes de realmente criar o índice de verdade.

Quando Usar Índices Virtuais?

Imagine que você está trabalhando em um ambiente de produção, cheio de consultas rodando o tempo todo. Você não quer arriscar criar um índice e de repente causar impacto em performance, certo? Nesse caso, os índices virtuais te dão uma mão. Eles permitem testar a viabilidade de um índice sem que ele exista fisicamente. Depois de testar, se fizer sentido, você pode criar o índice definitivo.

Em Qual Versão do Oracle os Índices Virtuais Estão Disponíveis?

Os índices virtuais foram introduzidos a partir do Oracle Database 11g. Se você estiver rodando qualquer versão do Oracle a partir dessa (11g, 12c, 18c, 19c, 21c), poderá aproveitar esse recurso.

Passo a Passo: Criando e Testando um Índice Virtual

Agora, vamos ver na prática como funciona. Vou te guiar em um exemplo com uma tabela que tem 1 milhão de registros, e a gente vai testar a criação e o impacto de um índice virtual.

1. Criando a Tabela e Inserindo Dados

Primeiro, criamos uma tabela chamada TABELA_DO_GUINA e inserimos 1 milhão de registros:

CREATE TABLE tabela_do_guina (
  id_cliente   NUMBER(10),
  nome         VARCHAR2(100),
  data_cadastro DATE
);

BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO tabela_do_guina (id_cliente, nome, data_cadastro) 
    VALUES (i, 'Cliente ' || i, SYSDATE - MOD(i, 1000));
    
    IF MOD(i, 10000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER , tabname => 'TABELA_DO_GUINA');

SELECT COUNT(1) FROM tabela_do_guina ;

Agora temos 1 milhão de registros, e nossa tabela está pronta para os testes.

2. Rodando uma Consulta Sem Índice

Vamos rodar uma consulta simples para buscar o registro pelo nome. Como ainda não temos nenhum índice, o Oracle vai fazer um full table scan (ou seja, ele vai varrer a tabela inteira). Após a execução, vamos verificar o plano de execução gerado com o DBMS_XPLAN:

COLUMN id_cliente FORMAT 9999999999; 
COLUMN nome FORMAT A30;              
COLUMN data_cadastro FORMAT A20;     
SET LINESIZE 150;  
SET PAGESIZE 100;  

SET TIMING ON;
SELECT * FROM tabela_do_guina WHERE nome = 'Cliente 500000';
SET TIMING OFF;

-- Agora verificamos o plano de execução:
EXPLAIN PLAN FOR
SELECT * FROM tabela_do_guina WHERE nome = 'Cliente 500000';

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Como esperado, o Oracle está realizando um TABLE ACCESS FULL.

3. Criando o Índice Virtual

Agora, vamos criar o índice virtual para testar se ele ajudaria a melhorar o desempenho da consulta. Para isso, usamos a opção NOSEGMENT, que significa que o índice não será criado fisicamente:

CREATE INDEX idx_virtual_nome  ON tabela_do_guina (nome)  NOSEGMENT;

SELECT segment_name, segment_type, tablespace_name
FROM dba_segments
WHERE segment_name = 'IDX_VIRTUAL_NOME';

Se o índice for realmente virtual, a consulta não deve retornar nenhum resultado. Isso confirma que o índice foi registrado apenas no dicionário de dados, sem ocupar espaço físico.

Beleza, o índice virtual foi criado! Mas ele ainda não está sendo utilizado nas consultas. Para que o Oracle o considere, precisamos ajustar um parâmetro de sessão.

4. Ativando o Índice Virtual

Para que o Oracle utilize esse índice virtual nas consultas, vamos ativar o parâmetro de sessão para considerá-lo:

ALTER SESSION SET "_USE_NOSEGMENT_INDEXES" = TRUE;

Pronto! Agora o Oracle vai considerar o índice virtual quando calcular o plano de execução das consultas.

5. Verificando o Plano de Execução com o Índice Virtual

Vamos rodar a mesma consulta de antes e verificar o plano de execução agora com o índice virtual habilitado:

COLUMN id_cliente FORMAT 9999999999; 
COLUMN nome FORMAT A30;              
COLUMN data_cadastro FORMAT A20;     
SET LINESIZE 150;  
SET PAGESIZE 100;  

SET TIMING ON;
SELECT * FROM tabela_do_guina WHERE nome = 'Cliente 500000';
SET TIMING OFF;

-- Agora verificamos o plano de execução:
EXPLAIN PLAN FOR
SELECT * FROM tabela_do_guina WHERE nome = 'Cliente 500000';

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Agora, podemos ver que o Oracle está utilizando o índice virtual, melhorando a performance da consulta.

6. Criando o Índice Físico

Depois de ver que o índice virtual realmente melhora o desempenho, podemos decidir criar o índice de verdade. Primeiro, vamos remover o índice virtual:

DROP INDEX idx_virtual_nome;

Agora, criamos o índice físico:

CREATE INDEX idx_nome  ON tabela_do_guina (nome);

7. Testando a Consulta com o Índice Físico

Agora, com o índice físico criado, vamos rodar novamente a consulta e comparar o tempo de execução:

COLUMN id_cliente FORMAT 9999999999; 
COLUMN nome FORMAT A30;              
COLUMN data_cadastro FORMAT A20;     
SET LINESIZE 150;  
SET PAGESIZE 100;  

SET TIMING ON;
SELECT * FROM tabela_do_guina WHERE nome = 'Cliente 500000';
SET TIMING OFF;

-- Agora verificamos o plano de execução:
EXPLAIN PLAN FOR
SELECT * FROM tabela_do_guina WHERE nome = 'Cliente 500000';

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

#BóBó

Os índices virtuais são uma ferramenta extremamente valiosa no Oracle, pois permitem testar o impacto de índices sem precisar criá-los fisicamente. Isso é especialmente útil em ambientes de produção, onde alterações podem ter efeitos indesejados. Com índices virtuais, você pode avaliar se um índice melhoraria a performance de uma consulta antes de aplicá-lo, evitando o risco de degradação da performance devido à criação desnecessária de índices.

Se você trabalha com ambientes de banco de dados críticos e precisa tomar decisões com cuidado, o uso de índices virtuais é um grande aliado para otimizar o desempenho de suas consultas sem comprometer a estabilidade do seu ambiente.

🐾🍀 #20240909 #DBASobrinho #GuinaNãoTinhaDó #BóBó #CaceteDeAgulha #VemOracleAce #DidiPrecisa #PedidoDoRodelaDeCaminhão #TemEventoNaIgreja #4MilParaArrumarCano #MinhaIrmaNãoReponde #ElaRepondeu🍀🐾

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.