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:

Consultas de pesquisas pontuais seletivas em tabelas. Uma consulta de pesquisa pontual retorna apenas uma ou um pequeno número de linhas distintas. Exemplos de casos de uso incluem:
- Usuários comerciais que precisam de tempos de resposta rápidos para painéis críticos com filtros altamente seletivos.
- Cientistas de dados que estão explorando grandes volumes de dados e procurando por subconjuntos específicos de dados.
- Aplicativos de dados que recuperam um pequeno conjunto de resultados com base em um extenso conjunto de predicados de filtragem.
Para obter mais informações, consulte Aceleração de consultas de pesquisa de pontos com otimização de pesquisa.
Pesquisas de dados de caracteres (texto) e endereços IPv4 executadas com as funções SEARCH e SEARCH_IP. Para obter mais informações, consulte Aceleração de consultas de texto com otimização de pesquisa.
Pesquisas de subcadeia de caracteres e expressões regulares (por exemplo, [ NOT ] LIKE, [ NOT ] ILIKE, [ NOT ] RLIKE etc.). Para obter mais informações, consulte Aceleração consultas de subcadeias de caracteres e expressões regulares com otimização de pesquisa.
Consultas sobre elementos nas colunas VARIANT, OBJECT e ARRAY (semiestruturadas) que usam os seguintes tipos de predicados:
- Predicados de igualdade.
- Predicados IN.
- Predicados que usam ARRAY_CONTAINS.
- Predicados que usam ARRAYS_OVERLAP.
- Predicados que usam pesquisa de texto completo com SEARCH.
- Predicados de expressão regular e subcadeia de caracteres
- Predicados que verificam valores NULL:
Para obter mais informações, consulte Aceleração de consultas de dados semiestruturados com otimização de pesquisa.
Consultas que utilizam funções geoespaciais selecionadas com valores GEOGRAPHY. Para obter mais informações, consulte Aceleração de consultas geoespaciais com otimização de pesquisa.

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. Você não precisa 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	Pesquisas por igualdade. Subcadeias de caracteres e pesquisas de expressões regulares. Pesquisas de dados de caracteres (texto) e endereços IPv4. Pesquisas de elementos em VARIANT. Pesquisas de colunas GEOGRAPHY usando funções geoespaciais. 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]	✔

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 SEARCH OPTIMIZATION à tabela usando ALTER TABLE:

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