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 de colunas VARIANT, OBJECT e ARRAY que utilizam certos tipos de predicados (predicados de igualdade, predicadosIN, predicados que utilizam ARRAY_CONTAINS e ARRAYS_OVERLAP, bem como 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 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:
Recurso |
Tipos de consulta suportados |
Outros casos de uso |
---|---|---|
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. |
|
Exibição materializada |
Nota: o desempenho só pode ser melhorado para o subconjunto de linhas e colunas incluído na exibição materializada. |
Exibições materializadas também podem ser usadas para definir diferentes chaves de clustering na mesma tabela de origem (ou um subconjunto dessa tabela) ou em conjunto com o achatamento de dados de variante/JSON. |
Clustering da tabela |
Nota: uma tabela pode ser clusterizada por 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 |
✔ |
✔ |
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>
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. (Este é um recurso de versão preliminar.)
GEOGRAPHY. (Este é um recurso de versão preliminar.)
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;
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;
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 queCOUNT(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:
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:
SPLIT_PART (em predicados de igualdade)
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 SUBSTRING(mycol);
As pesquisas 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).
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%'
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%'
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)+'
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.*$$RLIKE '.*country=(Germany|France|Spain).*'RLIKE '.*phone=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
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}.*'
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}.*'
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).*'
Padrões nos quais a subcadeia de caracteres é opcional:
RLIKE '.*[a-zA-z]+(string)?[0-9]+.*'
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}.*
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 caracteresst=CA
,st=AZ
oust=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.
Nota
Você deve habilitar este recurso para colunas ou campos específicos em colunas usando a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION. Por exemplo:
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user:uuid);
Consultas em uma coluna VARIANT não são melhoradas 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 consultas 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 consultas 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
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';
Predicados compatíveis para tipos de VARIANT¶
O serviço de otimização de pesquisa pode melhorar as consultas 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 dadosconstant
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;
Converter explicitamente um elemento em NUMBER com uma precisão e escala especificadas.
where src:location.temperature::NUMBER(8, 6) = 23.456789;
Corresponder um elemento com uma constante TEXT sem converter explicitamente o elemento.
where src:sender_info.ip_address = '123.123.123.123';
Converter explicitamente um elemento em TEXT.
where src:salesperson.name::TEXT = 'John Appleseed';
Converter explicitamente um elemento em DATE.
where src:events.date::DATE = '2021-03-26';
Converter explicitamente um elemento em TIME com uma escala especificada.
where src:events.time_info::TIME(6) = '01:02:03.456789';
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';
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 deconstant
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)
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)
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 camposrc:person:age
naquela coluna VARIANT.
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);
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);
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 GEOGRAPHYgeom
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);
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);
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))'));
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)
...
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)
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))'))
...
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.
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;
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';
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'
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 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 TABLE … ADD 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ê precisar de mais controle sobre quais colunas estão configuradas para otimização de pesquisa ou se precisar usar outros métodos de pesquisa além de EQUALITY, 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
Adição e manutenção da otimização de pesquisa a toda a tabela
Verificação de que a tabela está configurada para otimização de pesquisa
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.
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
Exemplo: suporte para predicados IN e de igualdade para todas as colunas aplicáveis
Exemplo: suporte para predicados IN e de igualdade para um campo em um VARIANT
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);
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);
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(*);
Observe o seguinte:
Como explicado na descrição da sintaxe para o método de pesquisa e destino, para um determinado método, não se pode especificar um asterisco e colunas específicas.
Embora a omissão da cláusula ON também configure a otimização de pesquisa para predicados IN e de igualdade em todas as colunas aplicáveis da tabela, há diferenças entre especificar e omitir a cláusula ON. Consulte Adição e manutenção da otimização de pesquisa a toda a tabela.
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);
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);
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);
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.
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;
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:
Exiba a configuração de otimização de pesquisa para a tabela e suas colunas.
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%';
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.
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.
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);
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 |
+---------------+----------+--------+------------------+--------+
Como trabalhar com tabelas otimizadas para pesquisa¶
Ao trabalhar com uma tabela que usa otimização de pesquisa, você precisa estar ciente dos efeitos sobre o serviço de otimização de pesquisa.
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.
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 tabelas nas quais o recurso está habilitado e o número de valores distintos nessas tabelas.
A quantidade de dados que muda nestas tabelas.
Importante
Os valores retornados pela função SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS são estimativas de melhor esforço com base na amostragem de um conjunto de dados parcial na tabela. Os custos estimados podem variar significativamente (até 50% ou, em casos raros, várias vezes) dos custos reais realizados.
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
eMERGE
: 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 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:
Mude para uma função que tenha os privilégios de remover a otimização de pesquisa da tabela.
Execute o comando ALTER TABLE … DROP SEARCH OPTIMIZATION sem a cláusula ON:
ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;
Por exemplo:
alter table test_table drop search optimization;
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);Adicione a propriedade SEARCHOPTIMIZATION à tabela:
alter table test_table add search optimization;As consultas a seguir podem utilizar o serviço de otimização de pesquisa:
select * from test_table where id = 2;select * from test_table where c2 = '1';select * from test_table where c3 = '1985-05-11';select * from test_table where c1 is null;select * from test_table where c1 = 4 and c3 = '1996-12-20';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;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;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;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;DELETE e UPDATE (e MERGE) também pode utilizar o serviço de otimização de pesquisa:
delete from test_table where id = 3;update test_table set c1 = 99 where id = 4;