Uso do serviço de otimização de pesquisa¶

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

Para melhorar o desempenho de pesquisas pontuais, o serviço de otimização de pesquisa se baseia em uma estrutura de dados persistentes que serve como um caminho de acesso de pesquisa otimizado.

Um serviço de manutenção que funciona em segundo plano é 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 tempo, dependendo do tamanho da tabela. O serviç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 ainda não foi atualizado, as consultas podem ser mais lentas, mas sempre retornarão resultados atualizados.

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:

• 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:

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

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.

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. Isso inclui os predicados que utilizam:

• LIKE

• LIKE ANY

• LIKE ALL

• ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART (em predicados de igualdade)

• RLIKE

• REGEXP

• REGEXP_LIKE

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

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).

Em contrapartida, 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:

• 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 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.

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

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

• Suporte para valores de VARIANT convertidos em TEXT

• Predicados compatíveis para tipos de VARIANT

• Limitações atuais no suporte para tipos de 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

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';

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.

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

As seções seguintes fornecem mais detalhes:

• Predicados compatíveis com funções geoespaciais

• Outras considerações de desempenho

• Exemplos que utilizam funções geoespaciais

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);

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

...
  ST_INTERSECTS(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)

...
  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)

...
  ST_CONTAINS(
    g1,
    TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))

...
  ST_WITHIN(
   TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
   g1)

...
  ST_WITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))

...
  ST_COVERS(
    TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
    g1)

...
  ST_COVERS(
    g1,
    TO_GEOGRAPHY('POINT(0 0)'))

...
  ST_COVEREDBY(
    TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
    g1)

...
  ST_COVEREDBY(
    g1,
    TO_GEOGRAPHY('POINT(-122.35 37.55)'))

...
  ST_DWITHIN(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1,
    100000)

...
  ST_DWITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    100000)

...
  ST_INTERSECTS(
    g1,
    ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))

...
  ST_INTERSECTS(
    ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)

...
  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)

...
  ST_WITHIN(
    ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
    g1)

...
  ST_COVERS(
    g1,
    ST_MAKEPOINT(0.2, 0.8))

...
  ST_INTERSECTS(
    g1,
    ST_MAKELINE(
      TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
      TO_GEOGRAPHY('POINT(0.8 0.2)')))

...
  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)

...
  ST_WITHIN(
    g1,
    TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))

...
  ST_COVERS(
    g1,
    ST_GEOGPOINTFROMGEOHASH('s00'))

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.

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.

O Query Acceleration Service não acelera consultas em tabelas que têm o serviço de otimização de pesquisa habilitado.

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 TABLE … ADD SEARCH OPTIMIZATION com a cláusula ON.

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.

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

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

• Exemplo: suporte para diferentes tipos de predicado

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

• Exemplo: suporte às funções geoespaciais

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

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

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

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

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

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

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 TABLE … ADD SEARCH OPTIMIZATION sem a cláusula ON.

Por exemplo:

alter table test_table add search optimization;

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.

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.

Modificação da tabela¶

Um caminho de acesso de pesquisa torna-se inválido se o valor padrão de uma coluna for alterado.

Para usar a otimização de pesquisa novamente após um caminho de acesso de pesquisa ter se tornado inválido, você deve descartar a propriedade SEARCH OPTIMIZATION e adicionar a propriedade SEARCH OPTIMIZATION novamente à tabela.

Um caminho de acesso de pesquisa permanece válido se você adicionar, descartar ou renomear uma coluna:

• Se você tiver habilitado a otimização de pesquisa para toda uma tabela sem definir as colunas específicas, quando você adiciona uma coluna a uma tabela, a nova coluna será adicionada automaticamente ao caminho de acesso de pesquisa. No entanto, se você tiver usado a cláusula ON ao habilitar a otimização de pesquisa para uma coluna, novas colunas não serão adicionadas automaticamente.

• Quando você descarta uma coluna de uma tabela, a coluna descartada é removida automaticamente do caminho de acesso de pesquisa.

• Renomear uma coluna não requer nenhuma mudança no caminho de acesso de pesquisa.

Se você descartar uma tabela, a propriedade SEARCH OPTIMIZATION e os caminhos de acesso de pesquisa também são descartados. Note que:

• Descartar a tabela restabelece imediatamente a otimização de pesquisa como uma propriedade da tabela.

• Quando você descarta uma tabela, o caminho de acesso de pesquisa tem o mesmo período de retenção de dados que a tabela.

Se você descartar a propriedade SEARCH OPTIMIZATION da tabela, o caminho de acesso de pesquisa será removido. Quando você adiciona a propriedade SEARCH OPTIMIZATION novamente à tabela, o serviço de manutenção precisa recriar o caminho de acesso da pesquisa. (Não há maneira de descartar a propriedade).

Clonagem de tabela, esquema ou banco de dados¶

Se você clonar uma tabela, esquema ou banco de dados, a propriedade SEARCH OPTIMIZATION e os caminhos de acesso de pesquisa de cada tabela também são clonados. (A clonagem de uma tabela, esquema ou banco de dados cria um clone zero-copy de cada tabela e seus correspondentes caminhos de acesso de pesquisa).

Note que se você usar CREATE TABLE … LIKE para criar uma nova tabela vazia com as mesmas colunas da tabela original, a propriedade SEARCH OPTIMIZATION não é copiada para a nova tabela.

Como trabalhar com tabelas em um banco de dados secundário (suporte para replicação de banco de dados)¶

Se uma tabela no banco de dados primário tiver a propriedade SEARCH OPTIMIZATION ativada, a propriedade é replicada para a tabela correspondente no banco de dados secundário.

Os caminhos de acesso de pesquisa no banco de dados secundário não são replicados, mas sim reconstruídos automaticamente. Observe que este processo incorre nos mesmos tipos de custos descritos em Gerenciamento dos custos do serviço de otimização de pesquisa.

Compartilhamento da tabela¶

Os provedores de dados podem usar Secure Data Sharing para compartilhar tabelas que tenham a otimização de pesquisa ativada.

Ao consultar tabelas compartilhadas, os consumidores de dados podem se beneficiar de qualquer melhoria de desempenho feita pelo serviço de otimização de pesquisa.

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 tabelas nas quais o recurso está habilitado e o número de valores distintos nessas tabelas.

• A quantidade de dados que muda nestas tabelas.

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.

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 TABLE … DROP 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   |
+---------------+-----------+-----------+-------------------+--------+

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);

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;

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;

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 TABLE … DROP 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.