Uso do 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 que utilizam um extenso conjunto de predicados para filtragem.

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

Neste tópico:

Explicação do serviço de 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.

  • Pesquisas de subcadeia de caracteres e expressões regulares (por exemplo, LIKE, ILIKE, RLIKE etc.).

  • Consultas em campos nas colunas VARIANT, OBJECT e ARRAY que usam determinados tipos de predicados:

    • Predicados de igualdade.

    • Predicados IN.

    • Predicados que usam ARRAY_CONTAINS.

    • Predicados que usam ARRAYS_OVERLAP.

    • Predicados de expressão regular e subcadeia de caracteres

    • Predicados que verificam valores NULL:

  • Consultas que utilizam funções geoespaciais selecionadas com valores GEOGRAPHY.

Uma vez identificadas as consultas que podem se beneficiar do serviço de otimização de pesquisa, é possível configurar a otimização de pesquisa para as colunas e tabelas utilizadas nessas consultas.

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

Para melhorar o desempenho das pesquisas de pontos, 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 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ê configura a otimização de pesquisa para uma tabela, o serviço de manutenção cria e preenche o caminho de acesso de pesquisa com os dados necessários para realizar as buscas.

    O processo de preenchimento dos dados pode levar algum tempo, dependendo do tamanho da tabela. O serviço de manutenção faz este trabalho em segundo plano e não bloqueia nenhuma operação simultânea na tabela.

  • 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 são executadas quando o caminho de acesso de pesquisa não foi atualizado, as consultas podem ser mais lentas, mas ainda retornarão resultados atualizados.

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.

Este caminho de acesso de pesquisa e o serviço de manutenção são transparentes para o usuário. Você não precisa criar um warehouse para o serviço que mantenha o caminho de acesso de pesquisa.

No entanto, observe que há um custo para o armazenamento e os recursos de computação para este serviço. Para obter mais detalhes, consulte Gerenciamento dos custos do serviço de otimização de pesquisa (neste tópico).

Considerando outras soluções para otimizar o desempenho das consultas

O serviço de otimização de pesquisa é uma das várias maneiras de otimizar o desempenho da consulta. As técnicas relacionadas 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.

Nota: o desempenho só pode ser melhorado para o subconjunto de linhas e colunas incluído na exibição materializada.

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.

Clusterização da tabela

  • Pesquisas por igualdade.

  • Pesquisas por intervalo.

Nota: 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 três 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 à reescrita de partições existentes em novas partições. (Note que este processo não introduz nenhuma linha nova. Isto apenas reorganiza as linhas existentes). Para obter mais detalhes, consulte Impacto do reclustering no crédito e no armazenamento.

Que privilégios de controle de acesso são necessários para o serviço de otimização de pesquisa?

Para adicionar, configurar ou remover a otimização de pesquisa de uma tabela, você deve ter os seguintes privilégios:

  • Você deve ter o privilégio OWNERSHIP para a tabela.

  • Você deve ter o privilégio ADD SEARCH OPTIMIZATION para o esquema que contém a tabela.

    GRANT ADD SEARCH OPTIMIZATION ON SCHEMA <schema_name> TO ROLE <role>
    
    Copy

Para usar o serviço de otimização de pesquisa para uma consulta, você só precisa de privilégios SELECT na tabela.

Você não precisa de nenhum privilégio adicional. Como a SEARCH OPTIMIZATION é uma propriedade da tabela, ela é automaticamente detectada e utilizada (se apropriado) ao consultar uma tabela.

Identificação das tabelas e colunas que se beneficiam da otimização de pesquisa

A otimização de pesquisa é mais indicada para melhorar o desempenho de uma consulta quando a tabela é consultada com frequência em colunas que não a chave de clustering primária.

O serviço de otimização de pesquisa atualmente oferece suporte aos tipos específicos de consultas para os seguintes tipos de dados:

  • Números de ponto fixo (por exemplo, INTEGER, NUMERIC).

  • DATE, TIME e TIMESTAMP.

  • VARCHAR.

  • BINARY.

  • VARIANT, OBJECT e ARRAY.

  • GEOGRAPHY.

Atualmente, o serviço de otimização de pesquisa não oferece suporte a tipos de dados de ponto flutuante, GEOMETRY ou outros tipos de dados não listados acima. O Snowflake pode acrescentar suporte para mais tipos de dados no futuro.

O serviço de otimização de pesquisa também não suporta agrupamentos.

Identificação de consultas que se beneficiam da otimização de pesquisa

A otimização de pesquisa é mais indicada para melhorar o desempenho dos seguintes tipos de consultas:

  • Uma consulta que normalmente é executada por alguns segundos ou mais (antes de aplicar a otimização de pesquisa). Na maioria dos casos, a otimização de pesquisa não melhorará substancialmente o desempenho de uma consulta que tem um tempo de execução de fração de segundo.

  • Uma consulta na qual pelo menos uma das colunas acessadas pela operação do filtro de consulta tem, no mínimo, 100.000 a 200.000 valores distintos.

    Para determinar o número de valores distintos, você pode usar um dos seguintes

    • Use APPROX_COUNT_DISTINCT para obter o número aproximado de valores distintos:

      select approx_count_distinct(column1) from table1;
      
      Copy
    • Use COUNT(DISTINCT <nome_da_coluna>) para obter o número real de valores distintos:

      select count(distinct c1), count (distinct c2)  from test_table;
      
      Copy

    Como você só precisa de uma aproximação do número de valores distintos, considere o uso de APPROX_COUNT_DISTINCT, que geralmente é mais rápido e mais barato do que COUNT(DISTINCT <nome_da_coluna>).

A otimização de pesquisa pode melhorar o desempenho dos seguintes tipos de consultas

Igualdade ou predicados IN

O serviço de otimização de pesquisa pode melhorar o desempenho das consultas que utilizam:

  • Predicados de igualdade (por exemplo, <nome_da_coluna> = <constante>).

  • Predicados que utilizam IN (consulte o exemplo).

Subcadeias de caracteres e expressões regulares

O serviço de otimização de pesquisa pode melhorar o desempenho das consultas com predicados que buscam subcadeias de caracteres ou usam expressões regulares em texto ou dados semiestruturados. (Para obter detalhes sobre como as pesquisas de subcadeia de caracteres funcionam com dados semiestruturados, consulte Pesquisa de subcadeia de caracteres em tipos VARIANT.)

Esse recurso inclui predicados que usam:

Nota

Você deve habilitar este recurso para colunas específicas usando a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION. (Ativar a otimização de pesquisa no nível da tabela não otimiza a pesquisa de subcadeia de caracteres.) Por exemplo:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(mycol);
Copy

As pesquisas de subcadeia de caracteres não são melhoradas se você omitir a cláusula ON.

O serviço de otimização de pesquisa pode melhorar o desempenho da busca de subcadeias de caracteres com 5 ou mais caracteres. (Subcadeias de caracteres mais seletivas podem resultar em melhor desempenho). O serviço de otimização de pesquisa não utiliza caminhos de acesso de pesquisa para os seguintes predicados porque a subcadeia de caracteres tem menos de 5 caracteres:

LIKE '%TEST%'
Copy

Para os seguintes predicados, o serviço de otimização de pesquisa pode otimizar essa consulta, usando caminhos de acesso de pesquisa para buscar as subcadeias de caracteres para SEARCH e OPTIMIZED. Entretanto, os caminhos de acesso de pesquisa não são usados para IS porque a subcadeia de caracteres tem menos de 5 caracteres.

LIKE '%SEARCH%IS%OPTIMIZED%'
Copy

Para consultas que utilizam RLIKE, REGEXP e REGEXP_LIKE contra o texto:

  • O argumento subject deve ser uma coluna TEXT em uma tabela que tenha a otimização de pesquisa habilitada.

  • O argumento pattern deve ser uma constante de cadeia de caracteres.

Para expressões regulares, o serviço de otimização de pesquisa funciona melhor quando:

  • O padrão contém pelo menos uma subcadeia de caracteres literal que tem 5 ou mais caracteres de comprimento.

  • O padrão especifica que a subcadeia de caracteres deve aparecer pelo menos uma vez.

Por exemplo, o seguinte padrão especifica que string deve aparecer uma ou mais vezes no sujeito:

RLIKE '(string)+'
Copy

O serviço de otimização de pesquisa pode melhorar o desempenho das consultas com os seguintes padrões, pois cada predicado especifica que uma subcadeia de caracteres de 5 ou mais caracteres deve aparecer pelo menos uma vez. (Note que o primeiro exemplo usa uma constante de cadeia de caracteres entre cifrões para evitar o escape dos caracteres de barras invertidas).

RLIKE $$.*email=[\w\.]+@snowflake\.com.*$$
Copy
RLIKE '.*country=(Germany|France|Spain).*'
Copy
RLIKE '.*phone=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
Copy

Em contraste, o serviço de otimização de pesquisa não utiliza caminhos de acesso de pesquisa para consultas com os seguintes padrões:

  • Padrões sem subcadeias de caracteres:

    RLIKE '.*[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
    
    Copy
  • Padrões que contêm apenas subcadeias de caracteres com menos de 5 caracteres:

    RLIKE '.*tel=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
    
    Copy
  • Padrões que utilizam o operador de alternância em que uma opção é uma subcadeia de caracteres com menos de 5 caracteres:

    RLIKE '.*(option1|option2|opt3).*'
    
    Copy
  • Padrões nos quais a subcadeia de caracteres é opcional:

    RLIKE '.*[a-zA-z]+(string)?[0-9]+.*'
    
    Copy

Mesmo quando os caracteres literais da subcadeia de caracteres são menos de 5, o serviço de otimização de pesquisa ainda pode melhorar o desempenho da consulta se a expansão da expressão regular produzir um literal da subcadeia de caracteres que tenha 5 caracteres ou mais.

Por exemplo, considere o padrão:

.*st=(CA|AZ|NV).*(-->){2,4}.*
Copy

Neste exemplo:

  • Embora os literais da subcadeia de caracteres (por exemplo, st=, CA etc.) sejam menos de 5, o serviço de otimização de pesquisa reconhece que a subcadeia de caracteres st=CA, st=AZ ou st=NV (cada um deles com 5 caracteres) deve aparecer no texto.

  • Da mesma forma, mesmo que o literal da subcadeia de caracteres --> seja menor que 5 caracteres, o serviço de otimização de pesquisa determina que a subcadeia de caracteres -->--> (que é maior que 5 caracteres) deve aparecer no texto.

O serviço de otimização de pesquisa pode usar caminhos de acesso de pesquisa para corresponder a essas subcadeias de caracteres, o que pode melhorar o desempenho da consulta.

Campos em colunas VARIANT

O serviço de otimização de pesquisa pode melhorar o desempenho de consultas de pesquisas pontuais e de subcadeia de caracteres em dados semiestruturados de tabelas do Snowflake (dados em colunas VARIANT, OBJECT e ARRAY).

Quando o suporte a VARIANT para o serviço de otimização de pesquisa é configurado para colunas em uma tabela, o serviço de otimização de pesquisa inclui automaticamente colunas VARIANT, OBJECT e ARRAY em um caminho de acesso de pesquisa. Isso se aplica até mesmo a colunas em que a estrutura está profundamente aninhada e em que a estrutura muda frequentemente. Você também pode ativar a otimização de pesquisa para campos específicos em uma coluna semiestruturada.

Nota

Você deve habilitar explicitamente este recurso para colunas ou campos específicos em colunas usando a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION. (Ativar a otimização de pesquisa no nível da tabela não a ativa para colunas VARIANT.) Por exemplo:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON SUBSTRING(c4:user.uuid);
Copy

Consultas em uma coluna VARIANT não são otimizadas se você omitir a cláusula ON.

As próximas seções fornecem mais detalhes sobre esse suporte:

Tipos de dados compatíveis para constantes e conversões em predicados para tipos de VARIANT

O serviço de otimização de pesquisa pode melhorar o desempenho de pesquisas pontuais de dados semiestruturados em que os seguintes tipos são usados para a constante e para a conversão implícita ou explícita do elemento:

  • FIXED (incluindo conversões que especificam uma precisão e escala válidas)

  • INTEGER

  • TEXT

  • DATE (incluindo conversões que especificam uma escala)

  • TIME (incluindo conversões que especificam uma escala)

  • TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ (incluindo conversões que especificam uma escala)

O serviço de otimização de pesquisa oferece suporte à conversão de tipos utilizando:

Suporte para valores de VARIANT convertidos em TEXT

O serviço de otimização de pesquisa também pode melhorar o desempenho das pesquisas pontuais nas quais as colunas VARIANT são convertidas em TEXT e são comparadas às constantes que são convertidas em TEXT.

Por exemplo, suponha que src seja uma coluna VARIANT contendo valores booleanos, de data e de hora que tenham sido convertidos em VARIANT:

create or replace TABLE test_table
(
    ID INTEGER,
    SRC VARIANT
);

insert into test_table select 1, to_variant('true'::boolean);          -- BOOLEAN
insert into test_table select 2, to_variant('2020-01-09'::date);       -- DATE
insert into test_table select 3, to_variant('01:02:03.899213'::time);  -- TIME
Copy

Para essa tabela, o serviço de otimização de pesquisa pode melhorar as seguintes consultas, que convertem a coluna VARIANT em TEXT e comparam a coluna com as constantes de cadeia de caracteres:

select * from test_table where src::TEXT = 'true';
select * from test_table where src::TEXT = '2020-01-09';
select * from test_table where src::TEXT = '01:02:03.899213';
Copy

Predicados suportados para pesquisas pontuais em tipos VARIANT

O serviço de otimização de pesquisa pode melhorar as consultas de pesquisas pontuais com os tipos de predicado listados abaixo. Nos exemplos abaixo, src é a coluna VARIANT e path_to_variant_field é um caminho para um campo na coluna VARIANT.

  • Predicados de igualdade da seguinte forma:

    where path_to_variant_field[::target_data_type] = constant


    target_data_type (se especificado) e o tipo de dados constant devem ser um dos tipos compatíveis listados acima.

    Note que :: é apenas um exemplo de uma das formas aceitas para converter o valor em um tipo específico.

    Por exemplo, o serviço de otimização de pesquisa oferece suporte para:

    • Corresponder um elemento com uma constante NUMBER sem converter explicitamente o elemento.

      where src:person.age = 42;
      
      Copy
    • Converter explicitamente um elemento em NUMBER com uma precisão e escala especificadas.

      where src:location.temperature::NUMBER(8, 6) = 23.456789;
      
      Copy
    • Corresponder um elemento com uma constante TEXT sem converter explicitamente o elemento.

      where src:sender_info.ip_address = '123.123.123.123';
      
      Copy
    • Converter explicitamente um elemento em TEXT.

      where src:salesperson.name::TEXT = 'John Appleseed';
      
      Copy
    • Converter explicitamente um elemento em DATE.

      where src:events.date::DATE = '2021-03-26';
      
      Copy
    • Converter explicitamente um elemento em TIME com uma escala especificada.

      where src:events.time_info::TIME(6) = '01:02:03.456789';
      
      Copy
    • Converter explicitamente um elemento em TIMESTAMP com uma escala especificada.

      where src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
      
      Copy
  • Predicados que utilizam as funções ARRAY, como:


    • where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)

      constant não deve ser NULL, e o tipo de dados de constant deve ser um dos tipos compatíveis listados acima.

      Note que :: é apenas um exemplo de uma das formas aceitas para converter o valor em um tipo específico.

      Por exemplo:

      where ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
      
      Copy
    • where ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), path_to_variant_field)

      O tipo de dados de cada constante (constant_1, constant_2 etc.) deve ser um dos tipos compatíveis listados acima. O ARRAY construído pode incluir constantes NULL.

      Por exemplo:

      where ARRAYS_OVERLAP(
          ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
      
      Copy
  • Os seguintes predicados que verificam valores NULL:

    • where IS_NULL_VALUE(path_to_variant_field)

      Note que IS_NULL_VALUE se aplica a valores JSON null, e não a valores SQL NULL.

    • where path_to_variant_field IS NOT NULL

    • where variant_column IS NULL

      em que variant_column se refere à coluna, e não a um caminho para um elemento nos dados semiestruturados.

      Por exemplo, o serviço de otimização de pesquisa oferece suporte ao uso da coluna VARIANT src, mas não ao caminho para o campo src:person.age naquela coluna VARIANT.

Pesquisa de subcadeia de caracteres em tipos VARIANT

O serviço de otimização de pesquisa pode otimizar pesquisas com caracteres curinga ou expressões regulares em colunas semiestruturadas (incluindo colunas ARRAY, OBJECT e VARIANT) ou campos nessas colunas. Isso inclui os predicados que utilizam:

Você pode ativar a otimização de pesquisa de subcadeia de caracteres para uma coluna ou para vários campos individuais em uma coluna. Por exemplo, a instrução a seguir permite a otimização de pesquisa de subcadeia de caracteres para um campo aninhado em uma coluna.

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
Copy

Uma vez criado o caminho de acesso à pesquisa, a seguinte consulta pode ser otimizada:

SELECT * FROM test_table WHERE col2:data.search LIKE '%optimization%';
Copy

No entanto, as consultas a seguir não são otimizadas porque não estão no campo especificado ao ativar a otimização de pesquisa.

SELECT * FROM test_table WHERE col2:name LIKE '%john%doe%';
SELECT * FROM test_table WHERE col2 LIKE '%hello%world%';
Copy

Você pode especificar vários campos a serem otimizados. Aqui, a otimização de pesquisa está habilitada para dois campos específicos na coluna col2.

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:name);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
Copy

Se você ativar a otimização de pesquisa para um determinado campo, ela será ativada para todos os subcampos. A segunda instrução ALTER TABLE abaixo é redundante porque a primeira instrução permite a otimização de pesquisa para todo o campo data, incluindo o campo search aninhado.

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
Copy

Da mesma forma, habilitar a otimização de pesquisa para uma coluna inteira permite que todas as pesquisas de subcadeia de caracteres nessa coluna sejam otimizadas, incluindo campos aninhados em qualquer profundidade dentro dela.

Como as constantes são avaliadas para pesquisas de cadeia de caracteres VARIANT

Ao avaliar a cadeia de caracteres constante em uma consulta (por exemplo, LIKE 'constant_string'), o serviço de otimização de pesquisa divide a cadeia de caracteres em tokens usando os seguintes caracteres como delimitadores:

  • Colchetes ([ e ]).

  • Chaves ({ e }).

  • Dois pontos (:).

  • Vírgulas (,).

  • Aspas duplas (").

Depois de dividir a cadeia de caracteres em tokens, o serviço de otimização de pesquisa considera apenas tokens com pelo menos 5 caracteres.

Exemplo de um predicado

Como o serviço de otimização de pesquisa trata a consulta

LIKE '%TEST%'

O serviço de otimização de pesquisa não utiliza caminhos de acesso de pesquisa para os seguintes predicados porque a subcadeia de caracteres tem menos de 5 caracteres.

LIKE '%SEARCH%IS%OPTIMIZED%'

O serviço de otimização de pesquisa pode otimizar essa consulta usando caminhos de acesso de pesquisa para procurar SEARCH e OPTIMIZED, mas não IS. IS tem menos de 5 caracteres.

LIKE '%HELLO_WORLD%'

O serviço de otimização de pesquisa pode otimizar essa consulta usando caminhos de acesso de pesquisa para HELLO_WORLD.

LIKE '%COL:ON:S:EVE:RYWH:ERE%'

O serviço de otimização de pesquisa divide essa cadeia de caracteres em COL, ON, S, EVE, RYWH, ERE. Como todos esses tokens têm menos de 5 caracteres, o serviço de otimização de pesquisa não pode otimizar esta consulta.

LIKE '%{\"KEY01\":{\"KEY02\":\"value\"}%'

O serviço de otimização de pesquisa divide isso em tokens KEY01, KEY02, VALUE e usa os tokens ao otimizar a consulta.

LIKE '%quo\"tes_and_com,mas,\"are_n\"ot\"_all,owed%'

O serviço de otimização de pesquisa divide essa cadeia de caracteres em nos tokens quo, tes_and_com, mas, are_n, ot, _all, owed. O serviço de otimização de pesquisa só pode usar tokens com 5 caracteres ou mais (tes_and_com, are_n) ao otimizar a consulta.

Limitações atuais no suporte para tipos de VARIANT

Atualmente, o suporte para tipos de VARIANT no serviço de otimização de pesquisa tem as seguintes limitações:

  • Predicados que utilizam XMLGET não têm suporte.

  • Predicados da forma variant_field IS NULL não têm suporte.

  • Predicados em que as constantes são resultados de subconsultas escalares não têm suporte.

  • Predicados que especificam caminhos para elementos que contêm subelementos não têm suporte.

As limitações atuais do serviço de otimização de pesquisa também se aplicam a esse recurso.

Funções geoespaciais

O serviço de otimização de pesquisa pode melhorar o desempenho de consultas com predicados que utilizam funções geoespaciais com objetos GEOGRAPHY.

Nota

Você deve habilitar este recurso para colunas específicas usando a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION. Por exemplo:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON GEO(mygeocol);
Copy

Consultas que utilizam funções geoespaciais não são melhoradas se você omitir a cláusula ON.

As seções seguintes fornecem mais detalhes:

Nota

Objetos GEOMETRY ainda não são compatíveis.

Predicados compatíveis com funções geoespaciais

Para consultas com predicados que utilizam as seguintes funções:

O serviço de otimização de pesquisa pode melhorar o desempenho se:

  • Uma expressão de entrada é uma coluna GEOGRAPHY em uma tabela, e

  • A outra expressão de entrada é uma constante de GEOGRAPHY (criada por meio de uma função de conversão ou construção).

  • Para ST_DWITHIN, o argumento da distância é uma constante REAL não negativa.

Observe que esse recurso tem as mesmas limitações que se aplicam ao serviço de otimização de pesquisa.

Outras considerações de desempenho

Como o serviço de otimização de pesquisa é projetado para predicados que são altamente seletivos e porque os predicados filtram por proximidade entre objetos geoespaciais, o clustering de objetos geoespaciais por proximidade na tabela pode resultar em melhor desempenho. Você pode agrupar seus dados, especificando a ordem de classificação ao carregar os dados ou usando o Clustering automático, dependendo se a tabela base muda com frequência:

Carregamento de dados pré-classificados

Se os dados em sua tabela base não mudarem com frequência, você poderá especificar a ordem de classificação ao carregar os dados. Você pode então ativar a otimização de pesquisa na coluna GEOGRAPHY. Por exemplo:

CREATE TABLE new_table AS SELECT * FROM source_table ORDER BY st_geohash(geom);
ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Copy

Após cada grande alteração feita em seus dados base, você poderá reordenar os dados manualmente.

Clustering automático

Se houver atualizações frequentes em sua tabela base, você poderá usar o comando ALTER TABLE … CLUSTER BY … para ativar o Clustering automático para que a tabela seja automaticamente clusterizada de novo à medida que muda.

O exemplo a seguir adiciona uma nova coluna geom_geohash do tipo VARCHAR e armazena o índice geohash ou H3 da coluna GEOGRAPHY geom nessa nova coluna. Em seguida, ele permite o Clustering automático na nova coluna como a chave de cluster. Esta abordagem reterá automaticamente as partes da tabela que mudarem.

CREATE TABLE new_table AS SELECT *, ST_GEOHASH(geom) AS geom_geohash FROM source_table;
ALTER TABLE new_table CLUSTER BY (geom_geohash);
ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Copy

Exemplos que utilizam funções geoespaciais

As seguintes instruções criam e configuram a tabela usada nos exemplos desta seção. A última instrução usa a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION para adicionar otimização de pesquisa para a coluna g1 GEOGRAPHY.

CREATE OR REPLACE TABLE geospatial_table (id NUMBER, g1 GEOGRAPHY);
INSERT INTO geospatial_table VALUES
  (1, 'POINT(-122.35 37.55)'),
  (2, 'LINESTRING(-124.20 42.00, -120.01 41.99)'),
  (3, 'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
ALTER TABLE geospatial_table ADD SEARCH OPTIMIZATION ON GEO(g1);
Copy
Exemplos de predicados compatíveis

A consulta a seguir é um exemplo de uma consulta compatível com o serviço de otimização de pesquisa. O serviço de otimização de pesquisa pode usar caminhos de acesso de pesquisa para melhorar o desempenho dessa consulta:

SELECT id FROM geospatial_table WHERE
  ST_INTERSECTS(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));
Copy

A seguir estão exemplos de predicados adicionais que são compatíveis com o serviço de otimização de pesquisa:

...
  ST_INTERSECTS(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)
Copy
...
  ST_CONTAINS(
    TO_GEOGRAPHY('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
    g1)
Copy
...
  ST_CONTAINS(
    g1,
    TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))
Copy
...
  ST_WITHIN(
   TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
   g1)
Copy
...
  ST_WITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
Copy
...
  ST_COVERS(
    TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
    g1)
Copy
...
  ST_COVERS(
    g1,
    TO_GEOGRAPHY('POINT(0 0)'))
Copy
...
  ST_COVEREDBY(
    TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
    g1)
Copy
...
  ST_COVEREDBY(
    g1,
    TO_GEOGRAPHY('POINT(-122.35 37.55)'))
Copy
...
  ST_DWITHIN(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1,
    100000)
Copy
...
  ST_DWITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    100000)
Copy
Exemplos de constantes de GEOGRAPHY de construção

A seguir, exemplos de predicados que utilizam diferentes funções de conversão e construção para a constante de GEOGRAPHY.

...
  ST_INTERSECTS(
    g1,
    ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
Copy
...
  ST_INTERSECTS(
    ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)
Copy
...
  ST_CONTAINS(
    ST_GEOGRAPHYFROMEWKT('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
    g1)
Copy
...
  ST_WITHIN(
    ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
    g1)
Copy
...
  ST_COVERS(
    g1,
    ST_MAKEPOINT(0.2, 0.8))
Copy
...
  ST_INTERSECTS(
    g1,
    ST_MAKELINE(
      TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
      TO_GEOGRAPHY('POINT(0.8 0.2)')))
Copy
...
  ST_INTERSECTS(
    ST_POLYGON(
      TO_GEOGRAPHY('SRID=4326;LINESTRING(0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)')),
    g1)
Copy
...
  ST_WITHIN(
    g1,
    TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))
Copy
...
  ST_COVERS(
    g1,
    ST_GEOGPOINTFROMGEOHASH('s00'))
Copy

Conjunções de predicados compatíveis (AND)

Para consultas que utilizam conjunções de predicados (ou seja, AND), o desempenho da consulta pode ser melhorado pela otimização de pesquisa se qualquer um dos predicados aderir às condições acima.

Por exemplo, suponha que uma consulta tenha:

where condition_x and condition_y

A otimização de pesquisa pode melhorar o desempenho se qualquer uma das condições retornar separadamente algumas linhas (ou seja, condition_x retorna algumas linhas ou condition_y retorna algumas linhas).

Se condition_x retorna algumas linhas mas condition_y retorna muitas linhas, o desempenho da consulta pode se beneficiar da otimização de pesquisa.

Consulte estes exemplos adicionais.

Disjunções de predicados compatíveis (OR)

Para consultas que utilizam disjunções de predicados (ou seja, OR), o desempenho da consulta pode ser melhorado pela otimização da pesquisa se todos os predicados aderirem às condições acima.

Por exemplo, suponha que uma consulta tenha:

where condition_x or condition_y

A otimização de pesquisa pode melhorar o desempenho se cada condição separadamente retornar algumas linhas (ou seja, condition_x retorna algumas linhas e condition_y retorna algumas linhas).

Se condition_x retorna algumas linhas, mas condition_y retorna muitas linhas, o desempenho da consulta não se beneficia da otimização de pesquisa.

No caso de disjunções, cada predicado isoladamente não é decisivo para a consulta. Os outros predicados precisam ser avaliados antes de ser possível determinar se a otimização de pesquisa pode melhorar o desempenho.

Junções

O serviço de otimização de pesquisa não melhora diretamente o desempenho das junções. Entretanto, ele pode melhorar o desempenho da filtragem das linhas de qualquer uma das tabelas antes da junção, se a tabela tiver a otimização de pesquisa habilitada e o predicado for seletivo.

Ambas as tabelas não precisam ter a otimização de pesquisa habilitada. A decisão de utilizar a otimização de pesquisa é tomada para cada tabela independentemente.

Exibições

O serviço de otimização de pesquisa pode melhorar indiretamente o desempenho das exibições (incluindo exibições seguras). Se uma tabela base para uma exibição tiver a otimização de pesquisa ativada e se a consulta usar um predicado seletivo para aquela tabela, o serviço de otimização de pesquisa pode melhorar o desempenho ao filtrar as linhas.

Todas as tabelas na exibição não precisam ter a otimização de pesquisa ativada. A otimização de pesquisa é realizada em cada tabela independentemente.

Tabelas com políticas de mascaramento e políticas de acesso a linhas

O serviço de otimização de pesquisa pode melhorar o desempenho de consultas para tabelas que utilizam políticas de mascaramento e políticas de acesso a linhas.

Nota

Quando a otimização de pesquisa é habilitada, um usuário que é impedido de ver um valor devido a uma política de mascaramento ou política de acesso a linhas pode ser capaz de deduzir com maior certeza se esse valor existe. Com ou sem otimização de pesquisa, as diferenças na latência da consulta podem fornecer dicas sobre a presença ou ausência de dados restritos por uma política, mas este efeito pode ser ampliado pela otimização de pesquisa em algumas situações.

Por exemplo, suponha que uma política de acesso a linhas impeça um usuário de acessar linhas com country = US, mas os dados não incluem linhas com country = US. Agora suponha que a otimização de pesquisa esteja habilitada para a coluna country e que o usuário execute uma consulta com WHERE country = US. A consulta retorna resultados vazios como esperado, mas a consulta pode ser executada mais rapidamente com otimização de pesquisa do que sem ela. Neste caso, o usuário pode inferir que os dados não contêm uma linha em que country = US com base no tempo necessário para executar a consulta.

Consultas incompatíveis com o serviço de otimização de pesquisa

O serviço de otimização de pesquisa não suporta o seguinte:

  • Tabelas externas.

  • Exibições materializadas.

  • Colunas definidas com uma cláusula COLLATE.

  • Concatenação de colunas.

  • Expressões analíticas.

  • Conversões em colunas de tabela (exceto para números de ponto fixo convertidos em cadeias de caracteres).

    Embora a otimização de pesquisa suporte predicados com conversões implícitas e explícitas sobre valores constantes, ela não suporta predicados que convertam valores na coluna da tabela real (exceto para conversões de INTEGER e NUMBER em VARCHAR).

    Por exemplo, os seguintes predicados têm suporte porque utilizam conversões implícitas e explícitas de valores constantes (e não valores na coluna da tabela):

    -- Supported predicate
    -- (where the string '2020-01-01' is implicitly cast to a date)
    WHERE timestamp1 = '2020-01-01';
    
    -- Supported predicate
    -- (where the string '2020-01-01' is explicitly cast to a date)
    WHERE timestamp1 = '2020-01-01'::date;
    
    Copy

    O seguinte predicado não tem suporte porque utiliza uma conversão dos valores na coluna da tabela:

    -- Unsupported predicate
    -- (where values in a VARCHAR column are cast to DATE)
    WHERE to_date(varchar_column) = '2020-01-01';
    
    Copy

    O serviço de otimização de pesquisa considera os valores originais da coluna, não os valores após a conversão. Como resultado, o serviço de otimização de pesquisa não é utilizado para consultas com esses predicados.

Como mencionado anteriormente, a exceção a esta regra é a de converter valores NUMBER ou INTEGER em valores VARCHAR na coluna da tabela. O serviço de otimização de pesquisa suporta este tipo de predicado:

-- Supported predicate
-- (where values in a numeric column are cast to a string)
WHERE cast(numeric_column as varchar) = '2'
Copy

A otimização de pesquisa não melhora o desempenho das consultas que utilizam Time Travel porque a otimização de pesquisa funciona somente com dados ativos.

Configuração da otimização de pesquisa para uma tabela

Nota

Adicionar a otimização de pesquisa a uma tabela grande (uma tabela contendo terabytes (TB) ou mais dados) pode resultar em um aumento imediato do consumo de crédito em um curto período de tempo.

Quando você adiciona a otimização de pesquisa a uma tabela, o serviço de manutenção começa imediatamente a construir os caminhos de acesso de pesquisa para a tabela em segundo plano. Se a tabela for grande, o serviço de manutenção pode paralisar maciçamente este trabalho, o que pode resultar em um aumento de custos em um curto período de tempo.

Antes de adicionar a otimização de pesquisa a uma tabela grande, faça uma estimativa destes custos para que você saiba o que esperar.

Para adicionar otimização de pesquisa a uma tabela, você usa o comando ALTER TABLEADD SEARCH OPTIMIZATION com ou sem a cláusula ON.

Usando o comando sem a cláusula ON estabelecerá caminhos de acesso de pesquisa para melhorar o desempenho das consultas usando o método de busca EQUALITY para todas as colunas que usam os tipos de dados suportados.

Se você quiser melhorar o desempenho para outros tipos de consultas ou se precisar de mais controle sobre quais colunas estão configuradas para otimização de pesquisa, use a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION.

Na cláusula ON em ADD SEARCH OPTIMIZATION, você especifica quais colunas devem ser habilitadas para otimização de pesquisa. Ao permitir a otimização de pesquisa para uma determinada coluna, você também pode especificar um método de pesquisa (por exemplo, EQUALITY para igualdade e pesquisas IN, GEO para pesquisas GEOGRAPHY e SUBSTRING para pesquisas de subcadeias).

Para gerenciar o custo da otimização de pesquisa, você pode remover a otimização de pesquisa de colunas específicas em que a otimização de pesquisa não é necessária.

As próximas seções explicam como configurar a otimização de pesquisa para uma tabela:

Configuração da otimização de pesquisa para colunas específicas

Para configurar a otimização de pesquisa para uma coluna específica, use o comando ALTER TABLEADD SEARCH OPTIMIZATION com a cláusula ON.

Nota

Ao executar esse comando, use uma função que tenha os privilégios de adicionar a otimização de pesquisa à tabela.

A cláusula ON especifica que você deseja configurar a otimização de pesquisa para colunas específicas. Para obter mais detalhes sobre a sintaxe, consulte a sessão sobre ALTER TABLE … ADD SEARCH OPTIMIZATION.

Nota

Se você quiser apenas aplicar a otimização de pesquisa para predicados IN e de igualdade a todas as colunas aplicáveis da tabela, consulte Adição e manutenção da otimização de pesquisa a toda a tabela.

Após executar esse comando, você pode verificar se as colunas foram configuradas para otimização de pesquisa.

As próximas seções contêm exemplos que demonstram como especificar a configuração para otimização de pesquisa:

Exemplo: suporte para predicados IN e de igualdade para colunas específicas

Para otimizar as pesquisas com predicados de igualdade para as colunas c1, c2 e c3 na tabela t1, execute a seguinte instrução:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3);
Copy

Você também pode especificar o mesmo método de pesquisa mais de uma vez na cláusula ON:

-- This statement is equivalent to the previous statement.
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);
Copy

Exemplo: suporte para predicados IN e de igualdade para todas as colunas aplicáveis

Para otimizar as pesquisas com predicados de igualdade para todas as colunas aplicáveis na tabela, execute a seguinte instrução:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(*);
Copy

Observe o seguinte:

Exemplo: suporte para diferentes tipos de predicado

Para otimizar as pesquisas com predicados de igualdade para as colunas c1 e c2, bem como pesquisas de subcadeias de caracteres para a coluna c3, execute a seguinte instrução:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2), SUBSTRING(c3);
Copy

Exemplo: suporte para predicados IN e de igualdade para um campo em um VARIANT

Para otimizar as pesquisas com predicados de igualdade no uuid do campo VARIANT aninhado no campo user na coluna VARIANT c4, execute a seguinte instrução:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);
Copy

Exemplo: suporte às funções geoespaciais

Para otimizar as pesquisas com previsões que utilizam funções geoespaciais com objetos GEOGRAPHY na coluna c1, execute a seguinte instrução:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON GEO(c1);
Copy

Adição e manutenção da otimização de pesquisa a toda a tabela

Se você só quiser especificar EQUALITY para todas as colunas dos tipos de dados compatíveis (exceto para VARIANT e GEOGRAPHY), use o comando ALTER TABLEADD SEARCH OPTIMIZATION sem a cláusula ON.

Nota

Ao executar esse comando, use uma função que tenha os privilégios de adicionar a otimização de pesquisa à tabela.

Por exemplo:

alter table test_table add search optimization;
Copy

Para obter mais informações sobre a sintaxe, consulte a seção sobre otimização de pesquisa em ALTER TABLE.

Após executar esse comando, você pode verificar se as colunas foram configuradas para otimização de pesquisa.

Efeito sobre as colunas adicionadas posteriormente

Após executar o comando ALTER TABLE … ADD SEARCH OPTIMIZATION sem a cláusula ON, quaisquer colunas que forem adicionadas posteriormente à tabela também serão configuradas para EQUALITY.

No entanto, se você executar ALTER TABLE … { ADD | DROP } SEARCH OPTIMIZATION com a cláusula ON na mesma tabela, quaisquer colunas que forem adicionadas posteriormente à tabela não serão configuradas para EQUALITY automaticamente. Você deve executar ALTER TABLE … ADD SEARCH OPTIMIZATION ON … para configurar essas colunas recém-adicionadas para EQUALITY.

Verificação de que a tabela está configurada para otimização de pesquisa

Para verificar se a tabela e suas colunas foram configuradas para otimização de pesquisa:

  1. Exiba a configuração de otimização de pesquisa para a tabela e suas colunas.

  2. Execute o comando SHOW TABLES para verificar se a otimização de pesquisa foi adicionada e para determinar quanto da tabela foi otimizada.

    Por exemplo:

    SHOW TABLES LIKE '%test_table%';
    
    Copy

    Na saída deste comando:

    • Verifique se SEARCH_OPTIMIZATION é ON, o que indica que a otimização de pesquisa foi adicionada.

    • Verifique o valor de SEARCH_OPTIMIZATION_PROGRESS. Isto especifica a porcentagem da tabela que foi otimizada até o momento.

      Quando a otimização de pesquisa é adicionada a uma tabela pela primeira vez, os benefícios de desempenho não aparecem imediatamente. O serviço de otimização de pesquisa começa a preencher os dados em segundo plano. Os benefícios aparecem cada vez mais à medida que a manutenção se aproxima do estado atual da tabela.

      Antes de executar uma consulta para verificar se a otimização de pesquisa está funcionando, aguarde até que isto mostre que a tabela foi totalmente otimizada.

  3. Execute uma consulta para verificar se a otimização de pesquisa está funcionando.

    Observe que o otimizador do Snowflake escolhe automaticamente quando usar o serviço de otimização de pesquisa para uma determinada consulta. Os usuários não podem controlar para qual consulta é utilizada a otimização de pesquisa.

    Escolha uma consulta que o serviço de otimização de pesquisa seja projetado para otimizar. Consulte Identificação das tabelas e colunas que se beneficiam da otimização de pesquisa.

  4. Na UI da Web, visualize o plano de consulta para esta consulta e verifique se o nó de consulta “Search Optimization Access” faz parte do plano de consulta.

Exibição da configuração de otimização de pesquisa para uma tabela

Para exibir a configuração de otimização de pesquisa de uma tabela, use o comando DESCRIBE SEARCH OPTIMIZATION.

Por exemplo, suponha que você execute a seguinte instrução para configurar a otimização de pesquisa de uma coluna:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1);
Copy

A execução DESCRIBE SEARCH OPTIMIZATION produz a seguinte saída:

DESCRIBE SEARCH OPTIMIZATION ON t1;

+---------------+----------+--------+------------------+--------+
| expression_id |  method  | target | target_data_type | active |
+---------------+----------+--------+------------------+--------+
| 1             | EQUALITY | C1     | NUMBER(38,0)     | true   |
+---------------+----------+--------+------------------+--------+
Copy

Gerenciamento dos custos do serviço de otimização de pesquisa

O serviço de otimização de pesquisa afeta os custos tanto para o armazenamento quanto para os recursos de computação:

  • Recursos de armazenamento: O serviço de otimização de pesquisa cria uma estrutura de dados do caminho de acesso de pesquisa que requer espaço para cada tabela na qual a otimização de pesquisa está habilitada. O custo de armazenamento do caminho de acesso de pesquisa depende de múltiplos fatores, inclusive:

    • O número de valores distintos (NDVs) na tabela. No caso extremo em que todas as colunas têm tipos de dados que utilizam o caminho de acesso de pesquisa, e todos os valores de dados em cada coluna são únicos, o armazenamento necessário pode ser tão grande quanto o tamanho da tabela original.

      Normalmente, porém, o tamanho é aproximadamente 1/4 do tamanho da tabela original.

  • Recursos de computação:

    • A adição de otimização de pesquisa a uma tabela consome recursos durante a fase inicial.

    • A manutenção do serviço de otimização de pesquisa também requer recursos. O consumo de recursos é maior quando há alta rotatividade (ou seja, quando grandes volumes de dados na tabela mudam). Estes custos são aproximadamente proporcionais à quantidade de dados ingeridos (adicionados ou alterados). As exclusões também têm algum custo.

      O clustering automático, embora melhore a latência das consultas em tabelas com otimização de pesquisa, pode aumentar ainda mais os custos de manutenção da otimização de pesquisa. Se uma tabela tiver uma alta taxa de variação, permitir o clustering automático e configurar a otimização de pesquisa da tabela pode resultar em custos de manutenção mais altos do que se a tabela for apenas configurada para otimização de pesquisa.

      O Snowflake garante o uso eficiente do crédito faturando sua conta somente para os recursos efetivamente utilizados. A cobrança é calculada em incrementos de 1 segundo.

      Consulte a “Serverless Feature Credit Table” na tabela de consumo de serviços do Snowflake para os custos por hora de computação.

      Uma vez ativado o serviço de otimização de pesquisa, você pode visualizar os custos para seu uso do serviço.

Dica

O Snowflake recomenda começar aos poucos com este recurso (ou seja, adicionar otimização de pesquisa a apenas algumas tabelas no início) e monitorar de perto os custos e benefícios.

Estimativa de custos

Para estimar o custo da adição da otimização de pesquisa a uma tabela e da configuração de colunas específicas para otimização de pesquisa, use a função SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS.

Em geral, os custos são proporcionais a:

  • O número de colunas nas quais o recurso está habilitado e o número de valores distintos nessas colunas.

  • A quantidade de dados que muda nestas tabelas.

Importante

Os cálculos de custo retornados pela função SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS são os melhores esforços. Os custos reais realizados podem variar significativamente (até 50% ou, em casos raros, várias vezes) dos custos estimados.

  • As estimativas de custo de criação e armazenamento são baseadas na amostragem de um subconjunto das linhas da tabela

  • As estimativas de custo de manutenção são baseadas em atividades recentes de criação, exclusão e atualização na tabela

Visualização dos custos

Você pode visualizar os custos de cobrança do serviço de otimização de pesquisa usando a interface da Web ou SQL. Consulte Exploração do custo de computação.

Redução de custos

Você pode controlar o custo do serviço de otimização de pesquisa selecionando cuidadosamente as tabelas e colunas para as quais habilitar a otimização de pesquisa.

Além disso, para reduzir o custo do serviço de otimização de pesquisa:

  • O Snowflake recomenda executar em lote as operações DML para a tabela:

    • DELETE: Se as tabelas armazenam dados para o período de tempo mais recente (por exemplo, o dia ou semana ou mês mais recente), então quando você ajusta sua tabela excluindo dados antigos, o serviço de otimização de pesquisa deve levar em conta as atualizações. Em alguns casos, você pode ser capaz de reduzir custos excluindo com menos frequência (por exemplo, diariamente e não de hora em hora).

    • INSERT UPDATE e MERGE: A combinação destes tipos de instruções DML na tabela pode reduzir o custo de manutenção através do serviço de otimização de pesquisa.

  • Se você fizer o reclustering de toda a tabela, considere descartar a propriedade SEARCH OPTIMIZATION para a tabela em questão antes do reclustering, e depois adicione a propriedade SEARCH OPTIMIZATION novamente à tabela após o reclustering.

  • Antes de permitir a otimização de pesquisa para pesquisas de subcadeia (ON SUBSTRING(col)) ou VARIANTs (ON EQUALITY(variant_col)), chame SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS para estimar os custos. A criação e manutenção inicial destes métodos de pesquisa pode ser intensiva do ponto de vista da computação, portanto, deve-se avaliar o compromisso entre desempenho e custo.

Remoção da otimização de pesquisa de colunas específicas ou de uma tabela inteira

Você pode remover a configuração de otimização de pesquisa para colunas específicas ou você pode remover a propriedade SEARCH OPTIMIZATION de toda a tabela.

Descarte da otimização de pesquisa para colunas específicas

Para descartar a configuração de otimização de pesquisa para colunas específicas, use o seguinte comando: comando ALTER TABLEDROP SEARCH OPTIMIZATION com a cláusula ON.

Por exemplo, suponha que a execução do comando DESCRIBE SEARCH OPTIMIZATION imprime as seguintes expressões:

DESCRIBE SEARCH OPTIMIZATION ON t1;

+---------------+-----------+-----------+-------------------+--------+
| expression_id |  method   | target    | target_data_type  | active |
+---------------+-----------+-----------+-------------------+--------+
|             1 | EQUALITY  | C1        | NUMBER(38,0)      | true   |
|             2 | EQUALITY  | C2        | VARCHAR(16777216) | true   |
|             3 | EQUALITY  | C4        | NUMBER(38,0)      | true   |
|             4 | EQUALITY  | C5        | VARCHAR(16777216) | true   |
|             5 | EQUALITY  | V1        | VARIANT           | true   |
|             6 | SUBSTRING | C2        | VARCHAR(16777216) | true   |
|             7 | SUBSTRING | C5        | VARCHAR(16777216) | true   |
|             8 | GEO       | G1        | GEOGRAPHY         | true   |
|             9 | EQUALITY  | V1:"key1" | VARIANT           | true   |
|            10 | EQUALITY  | V1:"key2" | VARIANT           | true   |
+---------------+-----------+-----------+-------------------+--------+
Copy

Para descartar a otimização de pesquisa de subcadeias de caracteres na coluna c2, execute a seguinte instrução:

ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON SUBSTRING(c2);
Copy

Para descartar a otimização de pesquisa para todos os métodos na coluna c5, execute a seguinte instrução:

ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON c5;
Copy

Como a coluna c5 está configurada para otimizar as pesquisas de igualdade e de subcadeia de caracteres, a instrução acima descarta a configuração para as pesquisas de igualdade e de subcadeia de caracteres para c5.

Para descartar a otimização de pesquisa por igualdade na coluna c1 e para descartar a configuração especificada pela expressão IDs 6 e 8, execute a seguinte instrução:

ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON EQUALITY(c1), 6, 8;
Copy

Para obter mais informações sobre a sintaxe, consulte a sessão sobre ALTER TABLE … DROP SEARCH OPTIMIZATION.

Remoção da otimização de pesquisa da tabela.

Para remover a propriedade SEARCH OPTIMIZATION de uma tabela:

  1. Mude para uma função que tenha os privilégios de remover a otimização de pesquisa da tabela.

  2. Execute o comando ALTER TABLEDROP SEARCH OPTIMIZATION sem a cláusula ON:

    ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;
    
    Copy

    Por exemplo:

    alter table test_table drop search optimization;
    
    Copy

    Para obter mais informações, consulte a sessão sobre ALTER TABLE … DROP SEARCH OPTIMIZATION.

Exemplos

O código a seguir mostra a criação e utilização de um serviço de otimização de pesquisa.

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

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.