Acelerando Consultas JSON no Oracle 19c com Índices

Olá, aficionados por bancos de dados! Hoje, vamos falar sobre como podemos melhorar a performance das consultas de dados JSON no Oracle 19c. Sabemos que, ao lidar com JSON no Oracle, precisamos de todos os truques possíveis para garantir consultas eficientes e rápidas. E adivinhem? A criação de índices pode ser o truque que você precisa!

Índices no JSON? Como assim?

O Oracle 19c permite que você crie índices funcionais em campos JSON, o que pode melhorar drasticamente o desempenho de consultas que acessam esses campos. Apesar de não ter um tipo de dados JSON nativo, o Oracle oferece várias funções para manipular dados JSON armazenados como VARCHAR2, CLOB ou BLOB.

Então, como podemos fazer isso? É mais fácil do que você imagina. Vamos usar nossa tabela ‘pessoas’ como exemplo.

A Tabela

Lembrando que temos uma tabela chamada ‘pessoas’ com uma coluna ‘dados’ que armazena nossos objetos JSON.

CREATE TABLE pessoas (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    dados CLOB CHECK (dados IS JSON),
    CONSTRAINT pessoas_pk PRIMARY KEY (id)
);

Agora, digamos que queremos acelerar as consultas ao campo ‘nome’ dentro do nosso JSON.

Povoando a tabela

Vamos adicionar 2000 registros na nossa tabela ‘pessoas’. Note que estamos adicionando diferentes nomes para demonstrar a eficiência do índice na consulta.

BEGIN
    FOR i IN 1..2000 LOOP
        INSERT INTO pessoas (dados) 
        VALUES ('{"nome": "Pessoa ' || i || '", "idade": ' || TRUNC(DBMS_RANDOM.VALUE(1, 101)) || ', "cidade": "Cidade ' || i || '"}');
    END LOOP;
    COMMIT;
END;
/

Este bloco PL/SQL irá inserir 2000 registros na nossa tabela ‘pessoas’ com nomes, idades e cidades distintas.

A função DBMS_RANDOM.VALUE gera um número decimal aleatório entre os dois argumentos que você fornecer. TRUNC é usado para remover a parte decimal, deixando apenas o inteiro. O intervalo dado é [1, 101) porque DBMS_RANDOM.VALUE pode retornar o valor de limite inferior, mas nunca retornará o limite superior. Portanto, ao passar 101 como o limite superior, garantimos que o número gerado esteja entre 1 e 100, inclusive.

Criando o Índice

Aqui está o comando SQL que você precisa para criar um índice funcional:

CREATE INDEX idx_nome_json ON pessoas (JSON_VALUE(dados, '$.nome'));

Isso cria um índice na expressão JSON_VALUE(dados, '$.nome'), que extrai o campo ‘nome’ dos nossos dados JSON. Agora, sempre que fizermos uma consulta que usa esse campo em uma cláusula WHERE, o Oracle poderá usar o índice para encontrar os registros correspondentes muito mais rapidamente.

Consultando dados com o Índice

Vamos fazer uma consulta no campo ‘nome’. Com o índice criado, o Oracle poderá buscar os registros de forma mais eficiente.

SELECT JSON_VALUE(dados, '$.nome') AS nome
FROM pessoas
WHERE JSON_VALUE(dados, '$.nome') = 'Pessoa 10';

Esse comando retornará o nome da ‘Pessoa 10’ muito mais rápido graças ao nosso índice.

Considerações Finais

Criar um índice pode melhorar o desempenho das consultas, mas lembre-se que eles não são gratuitos. Índices ocupam espaço no disco e podem aumentar o tempo necessário para INSERTs e UPDATEs, pois precisam ser atualizados sempre que um registro é alterado.

Use índices com sabedoria. Considere o trade-off entre o tempo de consulta e o tempo de inserção/atualização. E como sempre, teste tudo em um ambiente de desenvolvimento antes de implementar em produção.

Espero que esta dica ajude você a obter o melhor desempenho possível ao trabalhar com JSON no Oracle 19c. Até a próxima!