Habilitação e desabilitação da otimização de pesquisa

Para ativar a otimização de pesquisa, use uma função que tenha os privilégios necessários e, em seguida, ative-a para uma tabela inteira ou colunas específicas usando o comando ALTER TABLE … ADD SEARCH OPTIMIZATION.

Privilégios de controle de acesso requeridos

Para adicionar, configurar ou remover a otimização de pesquisa de uma tabela, você deve:

  • Ter privilégios OWNERSHIP na tabela.

  • Ter o privilégio ADD SEARCH OPTIMIZATION para o esquema que contém a tabela. Para conceder este privilégio:

    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.

Configuração da otimização de pesquisa

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.

Ao ativar a otimização de pesquisa, você tem a opção de ativá-la para uma tabela inteira ou para colunas específicas da tabela.

  • A ativação da otimização de pesquisa para uma tabela inteira permite consultas de pesquisa pontual em todas as colunas qualificadas.

    Para ativar a otimização de pesquisa para uma tabela inteira, use o comando ALTER TABLE … ADD SEARCH OPTIMIZATION sem a cláusula ON.

  • Ativar a otimização de pesquisa para colunas específicas evita gastar créditos na criação de caminhos de acesso de pesquisa para colunas que você não usa com frequência em consultas e também permite selecionar tipos adicionais de consultas a serem otimizadas para cada coluna, potencialmente aumentando ainda mais o desempenho.

    Para ativar a otimização de pesquisa para colunas específicas, especificando os tipos de consultas a serem otimizadas, 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 ou SUBSTRING para pesquisas de subcadeias). Você pode ativar mais de um método de pesquisa na mesma coluna.

Em geral, habilitar a otimização de pesquisa apenas para colunas específicas é a prática recomendada.

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

Depois de configurar a otimização de pesquisa, você pode inspecionar sua configuração para ter certeza de que está correta.

Você pode remover a otimização de pesquisa de colunas específicas ou tabelas inteiras quando descobrir que a otimização de pesquisa não oferece benefícios suficientes.

Habilitaçã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.

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 Habilitação da otimização de pesquisa para uma tabela inteira.

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 a diferentes predicados na mesma coluna

Para otimizar pesquisas por predicados de igualdade e predicados de subcadeia de caracteres na mesma coluna, c1, execute a seguinte instrução:

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), SUBSTRING(c1);
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

Habilitação da otimização de pesquisa para uma tabela inteira

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

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 otimização em 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 uma 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 de consultas que podem se beneficiar 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

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

Linguagem: Português