Serviço de otimização de pesquisa

O serviço de otimização de pesquisa pode melhorar significativamente o desempenho de certos tipos de busca e consultas analíticas. Há suporte para um amplo conjunto de predicados de filtragem (consulte Identificação de consultas que podem se beneficiar da otimização de pesquisa).

Nota

Para começar com um tutorial que compara o tempo de execução com e sem otimização de pesquisa, consulte Como começar com otimização de pesquisa

O serviço de otimização de pesquisa visa melhorar significativamente o desempenho de certos tipos de consultas em tabelas, incluindo:

Uma vez identificadas as consultas que podem se beneficiar do serviço de otimização de pesquisa, você pode ativar a otimização de pesquisa para as colunas e tabelas utilizadas nessas consultas.

O serviço de otimização de pesquisa é geralmente transparente para os usuários. As consultas funcionam da mesma forma que sem otimização de pesquisa; algumas são apenas mais rápidas. No entanto, a otimização de pesquisa tem efeitos em outras operações de tabela. Para obter mais informações, consulte Como trabalhar com tabelas otimizadas para pesquisa.

Como funciona o serviço de otimização de pesquisa

Para melhorar o desempenho das consultas de pesquisa, o serviço de otimização de pesquisa cria e mantém uma estrutura de dados persistente chamada caminho de acesso de pesquisa. O caminho de acesso de pesquisa mantém o controle de quais valores das colunas da tabela podem ser encontrados em cada uma de suas micropartições, permitindo que algumas micropartições sejam ignoradas durante a varredura da tabela.

Um serviço de manutenção é responsável pela criação e manutenção do caminho de acesso de pesquisa:

  • Quando você habilita a otimização de pesquisa, o serviço de manutenção cria e preenche o caminho de acesso de pesquisa com os dados necessários para realizar as buscas.

    A criação do caminho de acesso de pesquisa pode levar um tempo significativo, dependendo do tamanho da tabela. O serviço de manutenção faz este trabalho em segundo plano e não bloqueia nenhuma operação na tabela. As consultas não são aceleradas até que o caminho de acesso à pesquisa tenha sido totalmente criado.

  • Quando os dados na tabela são atualizados (por exemplo, carregando novos conjuntos de dados ou através de operações DML), o serviço de manutenção atualiza automaticamente o caminho de acesso de pesquisa para refletir as mudanças nos dados.

    Se as consultas forem executadas enquanto o caminho de acesso de pesquisa ainda estiver sendo atualizado, as consultas poderão ser executadas mais lentamente, mas ainda assim retornarão resultados corretos.

O progresso do serviço de manutenção de cada tabela aparece na coluna search_optimization_progress na saída de SHOW TABLES. Antes de medir a melhoria do desempenho da otimização de pesquisa em uma tabela recém-otimizada, certifique-se de que a coluna mostre que a tabela foi totalmente otimizada.

A manutenção do caminho de acesso de pesquisa é transparente. Não é necessário criar um warehouse virtual para executar o serviço de manutenção. No entanto, há um custo para os recursos de armazenamento e computação de manutenção. Para obter mais detalhes sobre custos, consulte Estimativa e gerenciamento de custos de otimização de pesquisa.

Outras opções para otimizar o desempenho da consulta

O serviço de otimização de pesquisa é uma das várias maneiras de otimizar o desempenho da consulta. Outras técnicas incluem:

  • Aceleração de consulta.

  • Clustering de uma tabela.

  • Criação de uma ou mais exibições materializadas (clusterizadas ou não).

Cada uma tem vantagens diferentes, como mostra a tabela a seguir:

Recurso

Tipos de consulta suportados

Notas

Serviço de otimização de pesquisa

O serviço de otimização de pesquisa pode melhorar o desempenho destes tipos de pesquisa para os tipos de dados suportados.

Serviço de aceleração de consultas

Consultas com filtros ou agregação. Se a consulta incluir LIMIT, ela também deverá incluir ORDER BY.
Os filtros devem ser altamente seletivos e a cláusula ORDER BY deve ter uma cardinalidade baixa.

A aceleração de consultas funciona bem com análises ad-hoc e consultas com volume de dados imprevisível,
além de consultas com grandes varreduras e filtros seletivos.

A aceleração de consultas e a otimização de pesquisa são complementares. Ambos podem acelerar a mesma consulta. Consulte Compatibilidade com a aceleração de consulta.

Exibição materializada

  • Pesquisas por igualdade.

  • Pesquisas por intervalo.

  • Operações de classificação.

Você também pode usar exibições materializadas para definir diferentes chaves de clustering na mesma tabela de origem (ou em um subconjunto dessa tabela) ou para armazenar dados nivelados JSON ou variantes, de modo que só precisem ser nivelados uma vez.

As exibições materializadas melhoram o desempenho somente para o subconjunto de linhas e colunas incluídas na exibição materializada.

Clusterização da tabela

  • Pesquisas por igualdade.

  • Pesquisas por intervalo.

Uma tabela pode ser clusterizada somente em uma única chave, que pode conter uma ou mais colunas ou expressões.

A tabela seguinte mostra quais dessas otimizações têm custos de armazenamento ou computação:

Custo de armazenamento

Custo de computação

Serviço de otimização de pesquisa

Serviço de aceleração de consultas

Exibição materializada

Clustering da tabela

1

1

O processo de reclustering pode aumentar o tamanho do armazenamento Fail-safe devido à regravação de partições existentes em novas partições. (O reclustering não introduz novas linhas, apenas reorganiza as linhas existentes.) Para obter mais detalhes, consulte Impacto do reclustering no crédito e no armazenamento.

Compatibilidade com a aceleração de consulta

A otimização de pesquisa e a aceleração da consulta podem trabalhar juntas para otimizar o desempenho da consulta. Primeiro, a otimização de pesquisa pode eliminar as micropartições que não são necessárias para uma consulta. Então, para as consultas elegíveis, a aceleração de consultas pode transferir partes do restante do trabalho para recursos de computação compartilhados fornecidos pelo serviço.

O desempenho das consultas aceleradas por ambos os serviços varia de acordo com a carga de trabalho e os recursos disponíveis.

Exemplos

Comece criando uma tabela com dados:

create or replace table test_table (id int, c1 int, c2 string, c3 date) as
select * from values
  (1, 3, '4',  '1985-05-11'),
  (2, 4, '3',  '1996-12-20'),
  (3, 2, '1',  '1974-02-03'),
  (4, 1, '2',  '2004-03-09'),
  (5, null, null,  null);
Copy

Adicione a propriedade SEARCHOPTIMIZATION à tabela:

alter table test_table add search optimization;
Copy

As consultas a seguir podem utilizar o serviço de otimização de pesquisa:

select * from test_table where id = 2;
Copy
select * from test_table where c2 = '1';
Copy
select * from test_table where c3 = '1985-05-11';
Copy
select * from test_table where c1 is null;
Copy
select * from test_table where c1 = 4 and c3 = '1996-12-20';
Copy

A consulta seguinte pode utilizar o serviço de otimização de pesquisa porque a conversão implícita está na constante, não na coluna:

select * from test_table where c2 = 2;
Copy

Os seguintes não podem utilizar o serviço de otimização de pesquisa porque a conversão está na coluna da tabela:

select * from test_table where cast(c2 as number) = 2;
Copy

Uma cláusula IN é suportada pelo serviço de otimização de pesquisa:

select id, c1, c2, c3
    from test_table
    where id IN (2, 3)
    order by id;
Copy

Se os predicados forem suportados individualmente por um serviço de otimização de pesquisa, eles poderão ser unidos pela conjunção AND e ainda serem suportados pelo serviço de otimização de pesquisa:

select id, c1, c2, c3
    from test_table
    where c1 = 1
       and
          c3 = TO_DATE('2004-03-09')
    order by id;
Copy

DELETE e UPDATE (e MERGE) também pode utilizar o serviço de otimização de pesquisa:

delete from test_table where id = 3;
Copy
update test_table set c1 = 99 where id = 4;
Copy